PostgreSQL query to count/group by day and display days with no data

Question

I need to create a PostgreSQL query that returns

• a day
• the number of objects found for that day

It's important that every single day appear in the results, 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.)

First, I found a sql query to generate a range of days, with which I can join:

SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
AS date 
FROM generate_series(0, 365, 1) 
AS offs

Results in:

    date    
------------
 2013-03-28
 2013-03-27
 2013-03-26
 2013-03-25
 ...
 2012-03-28
(366 rows)

Now I'm trying to join that to a table named 'sharer_emailshare' which has a 'created' column:

Table 'public.sharer_emailshare'
column    |   type  
-------------------
id        | integer
created   | timestamp with time zone
message   | text
to        | character varying(75)

Here's the best GROUP BY query I have so far:

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;

The results:

    date    | count 
------------+-------
 2013-03-27 |    11
 2013-03-24 |     2
 2013-02-14 |     2
(3 rows)

Desired results:

    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)

If I understand correctly this is because I'm using a plain (implied INNER) JOIN, and this is the expected behavior, as discussed in the postgres docs.

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.

The guy asking this question found his answer, with Postgres, but put it on a pastebin link that expired some time ago.

I've tried to switch to LEFT OUTER JOIN, RIGHT JOIN, RIGHT OUTER JOIN, CROSS JOIN, use a CASE statement to sub in another value if null, COALESCE to provide a default value, etc, but I haven't been able to use them in a way that gets me what I need.

Any assistance is appreciated! And I promise I'll get around to reading that giant PostgreSQL book soon ;)

Answer 1

You just need a left outer join instead of an inner join:

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 LEFT OUTER JOIN
     sharer_emailshare se 
     ON d.date = to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
GROUP BY d.date;