title: PostgreSQL query to count/group by day and display days with no data
body: <p>I need to create a PostgreSQL query that returns</p>
  
  <ul>
  <li>a day</li>
  <li>the number of objects found for that day</li>
  </ul>
  
  <p>It's important that <strong>every single day appear in the results</strong>, even if no objects were found on that day. (This has been discussed before but I haven't been able to get things working in my specific case.)</p>
  
  <p>First, I found a <a href="https://stackoverflow.com/questions/346132/postgres-how-to-return-rows-with-0-count-for-missing-data">sql query to generate a range of days</a>, with which I can join:</p>
  
  <pre><code>SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
  AS date 
  FROM generate_series(0, 365, 1) 
  AS offs
  </code></pre>
  
  <p>Results in:</p>
  
  <pre><code>    date    
  ------------
   2013-03-28
   2013-03-27
   2013-03-26
   2013-03-25
   ...
   2012-03-28
  (366 rows)
  </code></pre>
  
  <p>Now I'm trying to join that to a table named 'sharer_emailshare' which has a 'created' column:</p>
  
  <pre><code>Table 'public.sharer_emailshare'
  column    |   type  
  -------------------
  id        | integer
  created   | timestamp with time zone
  message   | text
  to        | character varying(75)
  </code></pre>
  
  <p>Here's the best <code>GROUP BY</code> query I have so far: </p>
  
  <pre><code>SELECT d.date, count(se.id) FROM (
      select to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
      AS date 
      FROM generate_series(0, 365, 1) 
      AS offs
      ) d 
  JOIN sharer_emailshare se 
  ON (d.date=to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
  GROUP BY d.date;
  </code></pre>
  
  <p>The results:</p>
  
  <pre><code>    date    | count 
  ------------+-------
   2013-03-27 |    11
   2013-03-24 |     2
   2013-02-14 |     2
  (3 rows)
  </code></pre>
  
  <p>Desired results:</p>
  
  <pre><code>    date    | count 
  ------------+-------
   2013-03-28 |     0
   2013-03-27 |    11
   2013-03-26 |     0
   2013-03-25 |     0
   2013-03-24 |     2
   2013-03-23 |     0
   ...
   2012-03-28 |     0
  (366 rows)
  </code></pre>
  
  <p>If I understand correctly this is because I'm using a plain (implied <code>INNER</code>) <code>JOIN</code>, and this is the expected behavior, as <a href="http://www.postgresql.org/docs/8.3/static/queries-table-expressions.html" rel="noreferrer">discussed in the postgres docs</a>.</p>
  
  <p>I've looked through dozens of StackOverflow solutions, and all the ones with working queries seem specific to MySQL/Oracle/MSSQL and I'm having a hard time translating them to PostgreSQL.</p>
  
  <p>The guy asking <a href="https://stackoverflow.com/questions/346132/postgres-how-to-return-rows-with-0-count-for-missing-data">this question</a> found his answer, with Postgres, but put it on a pastebin link that expired some time ago.</p>
  
  <p>I've tried to switch to <code>LEFT OUTER JOIN</code>, <code>RIGHT JOIN</code>, <code>RIGHT OUTER JOIN</code>, <code>CROSS JOIN</code>, use a <code>CASE</code> statement to sub in another value if null, <code>COALESCE</code> to provide a default value, etc, but I haven't been able to use them in a way that gets me what I need.</p>
  
  <p>Any assistance is appreciated! And I promise I'll get around to reading that giant PostgreSQL book soon ;)</p>