Search code examples
sqlpostgresqltimestampepoch

sql return dates where there are no results

I want to get a table of results showing the dates that X has entries

SELECT count(*),
       date_column
FROM myTable
WHERE X
GROUP BY date_column
ORDER BY date_column DESC

This works, but I would also like to see the dates where X does not have entries, in my use case this would be intermediary dates.

So for instance 2013-3-10 would be in the results, but the next date would be 2013-3-5, yet I need my result to also return the days where count = 0, so in this case, the 6th, 7th, 8th and 9th

how would I format my query to include those extra times?

Solution

  • I mocked up a simple example:

    SELECT q.date_column, count(f.id) FROM
(SELECT
 generate_series(min(date_column),max(date_column), '1 day') AS date_column
 FROM mytable) AS q
LEFT JOIN mytable AS f
ON q.date_column=f.date_column
GROUP BY q.date_column ORDER BY q.date_column;

    This generates all dates in the needed range. make sure not to do count(*) or you'll get 1 instead of 0 http://sqlfiddle.com/#!1/fd4ff/1