Explore our sites

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

In Postgres I am aggregating a total amount, separating days, and then dumping that data into chart. Example query looks like this:

SELECT 
EXTRACT(YEAR FROM post_created_date) as report_year, EXTRACT(WEEK FROM post_created_date) AS regweek, COUNT(post_id) as post_count 
FROM TableA 
GROUP BY report_year, regweek

This works fine, but on some days there is no data, giving the chart gaps

1/14  -  5
1/15  -  7
1/18  -  4

How can I also include dates that have no event report 0 on that date?

share|improve this question
1  
You need a calendar table (which can be generated on the fly by generate_series() ) –  wildplasser yesterday
add comment

1 Answer

up vote 1 down vote

You need to use left outer join for this purpose. The idea is to create all combinations of year and week and then join to your query. This is pretty easy in Postgres with generate_series(). Something like:

select yw.yr, yw.wk, coalesce(r.post_count, 0) as post_count
from (select distinct EXTRACT(YEAR FROM post_created_date) as yr,
             generate_series(1, 52) as wk
      from TableA
     ) yw left outer join
     (SELECT EXTRACT(YEAR FROM post_created_date) as report_year,
             EXTRACT(WEEK FROM post_created_date) AS regweek, COUNT(post_id) as post_count 
      FROM TableA 
      GROUP BY report_year, regweek
     ) r
     on yw.yr = report_year and yw.wk = regweek;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.