Postgresql Serial Daily Count of Records

I am trying to get a total count of records from 1st Jan till date, without skipping dates and returning 0 for dates that have no records.

I have tried the following: orders is an example table and orderdate is a timestamp column

with days as (
  select generate_series(
    date_trunc('day','2020-01-01'::timestamp),
    date_trunc('day', now()),
    '1 day'::interval
  ) as day
)
select
  days.day,
  count(orders.id)
from days
left outer join orders on date_trunc('day', orders.orderdate) = days.day
where orders.orders_type='C'
group by 1

The issue is that dates are skipped. yet if i execute:

select generate_series(
    date_trunc('day','2020-01-01'::timestamp),
    date_trunc('day', now()),
    '1 day'::interval
  )

i get the right series with no dates skipped.

Answer

The where condition should belong to the on clause of the left join, ie this:

from days
left outer join orders on date_trunc('day', orders.orderdate) = days.day
where orders.orders_type='C'

Should be written:

from days
left outer join orders 
    on  date_trunc('day', orders.orderdate) = days.day
    and orders.orders_type='C'

Notes:

• you don’t actually need a cte here, you can put generate_series() directly in the from clause

• the date join condition can be optimized to an index-friendly expression that avoids date_trunc()

• table aliases make the query easier to read and write

You could write the query as:

select d.day, count(o.id)
from generate_series(date '2020-01-01', now(), '1 day') as d(day)
left outer join orders o
    on  o.orderdate >= d.day
    and o.orderdate <  d.day + '1 day'::interval
    and o.orders_type='C'
group by d.day