In my table I have some entries which - by the table's date column - is not older than 2016-01-04 (January 4, 2016). Now I would like to make a query which more or less counts the number of rows which have a specific date value, but I'd like this query to be able to return a 0 count for dates not present in table.
I have this:
with date_count as (select '2016-01-01'::date + CAST(offs || ' days' as
interval) as date from generate_series(0, 6, 1) AS offs ) select
date_count.date, count(allocation_id) as packs_used from medicine_allocation,
date_count where site_id = 1 and allocation_id is not null and timestamp
between date_count.date and date_count.date + interval '1 days' group by
date_count.date order by date_count.date;
This surely gives me a nice aggregated view of the date in my table, but since no rows are from before January 4 2016, they don't show in the result:
"2016-01-04 00:00:00";1
"2016-01-05 00:00:00";2
"2016-01-06 00:00:00";4
"2016-01-07 00:00:00";3
I would like this:
"2016-01-01 00:00:00";0
"2016-01-02 00:00:00";0
"2016-01-03 00:00:00";0
"2016-01-04 00:00:00";1
"2016-01-05 00:00:00";2
"2016-01-06 00:00:00";4
"2016-01-07 00:00:00";3
I have also tried right join on the cte, but this yields the same result. I cannot quite grasp how to do this... any help out there?
Best, Janus
You simply need a left join:
with date_count as (
select '2016-01-01'::date + CAST(offs || ' days' as
interval) as date
from generate_series(0, 6, 1) AS offs
)
select dc.date, count(ma.allocation_id) as packs_used
from date_count dc left join
medicine_allocation ma
on ma.site_id = 1 and ma.allocation_id is not null and
ma.timestamp between dc.date and dc.date + interval '1 days'
group by dc.date
order by dc.date;
A word of advice: Never use commas in the FROM clause. Always use explicit JOIN syntax.
You will also notice that the where conditions were moved to the ON clause. That is necessary because they are on the second table.