Postgres entries per week, for every week

Question

I'd like to return the count of entries per week, for every calendar week. My current code:

SELECT COUNT(*) AS count, date_trunc('week', occured_on) AS week
from incidents
GROUP BY date_trunc('week', occured_on)
ORDER BY date_trunc('week', occured_on);

This returns:

count  |        week         
-------+---------------------
     1 | 2009-09-28 00:00:00
     2 | 2009-10-19 00:00:00
     6 | 2009-10-26 00:00:00
     3 | 2009-11-02 00:00:00
     6 | 2009-11-09 00:00:00
    22 | 2009-11-16 00:00:00

Skipping a week where no incidents occurred. How do I get the count of 0 for that week, showing the total number of entries for each calendar week, rather than each week an incident occurred?

Answer 1

You can do this by generating the series of weeks and then using left join before the aggregation. The following generates the series using CTEs. This is not necessary, but I think it shows the logic being used:

with dates as (
      select min(date_trunc('week', occured_on)) as startw,
             max(date_trunc('week', occured_on)) as endw
      from incidents
     ),
     weeks as (
      select generate_series(startw, endw, '7 days') as week
      from dates
     )
select w.week, count(i.occured_on)
from weeks w left outer join
     incidents i
     on date_trunc('week', i.occured_on) = w.week
group by w.week;

SQL Fiddle is here.