CODEWITHSUNDEEP
sqlpostgresqlselectaggregate-functions
m4tm4t
m4tm4t

Reputation: 2381

PostgreSQL aggregation with missing dates

I'm trying this query

SELECT date_trunc('day', commit_at) AS day, count(*)
  FROM commits
  GROUP BY date_trunc('day', commit_at)
  ORDER BY date_trunc('day', commit_at) ASC;

and it return

      day         | count 
---------------------+-------
2015-05-18 00:00:00 |     5
2015-05-19 00:00:00 |     2
2015-05-21 00:00:00 |     2
(3 lignes)

The question is: How I can force empty days to be in the results ?

      day         | count 
---------------------+-------
2015-05-18 00:00:00 |     5
2015-05-19 00:00:00 |     2
2015-05-20 00:00:00 |     0
2015-05-21 00:00:00 |     2
(3 lignes)

Upvotes: 4

Views: 1661

Answers (1)

Mureinik
Mureinik

Reputation: 311308

One way of doing this is using nu generate_series to generate all the days in between the minimal and maximal date, and then join it to the aggregate query:

SELECT    DATE_TRUNC ('day', 
                      GENERATE_SERIES (MIN(commit_at), MAX(commit_at), '1 day') 
             AS day, 
          COALESCE (cnt, 0)
FROM      commits
LEFT JOIN (SELECT   DATE_TRUNC('day', commit_at) AS cday
           FROM     commits
           GROUP BY DATE_TRUNC('day', commit_at)) agg ON day = cday
ORDER BY  1 ASC

Upvotes: 5

Related Questions