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

group-by, join, postgresql, sql

Solution

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;

Problem

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 ;)

Original source

Related problems