Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel