select to_char(date_table.backcount,'FMDay'), to_char(date_table.backcount,'FMDD FMMon FMYYYY'), sum(case when yt is null then 0 else 1 end) from ( select date_trunc('day', current_timestamp) - x * interval '1 day' as backcount from generate_series(0, (select cast(extract(doy from cast('20081231' as date)) as integer))) as f(x) -- count back one year ) as date_table left join your_table yt on (date_trunc('day', yt.date::timestamptz) = date_table.backcount) group by date_table.backcount order by date_table.backcount desc