Postgres Fill In Empty Data When Aggregating

Question

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?

You need a calendar table (which can be generated on the fly by generate_series() ) — wildplasser
– wildplasser, Commented Jan 19, 2014 at 14:38

Gordon Linoff · Accepted Answer · 2014-01-19 17:00:01Z

1

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;

answered Jan 19, 2014 at 17:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Fill In Empty Data When Aggregating

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related