CODEWITHSUNDEEP
sqlpostgresql
equivalentideas
equivalentideas

Reputation: 325

Postgres entries per week, for every week

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?

Upvotes: 6

Views: 4140

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

SELECT  my_weeks.week_number  AS week , IFNULL(i.count,0)
from 
(
 SELECT generate_series (1,54,1) AS week_number
) my_weeks
LEFT OUTER JOIN 
(
  SELECT COUNT(*) AS count, date_trunc('week', occured_on) AS week
  from incidents
  GROUP BY date_trunc('week', occured_on)
) i
ON my_weeks.week_number = i.week
ORDER BY my_weeks.week_number ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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.

Upvotes: 15

Related Questions