with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day ) select 'Inspections'::text as data_label, count(i.reporting_id) as daily_count, d.day as date_column from days d left join inspection i on i.close_case_date = d.day group by d.day order by d.day
This returns a set that looks like this:
data_label | daily_count | date_column Inspections 1 01/01/13 Inspections 2 01/02/13 Inspections 4 01/04/13 Inspections 8 01/06/13
Note the 1 and 2 day gaps in the record set. I need to generate a set with those values filled in with 0 like so:
data_label | daily_count | date_column Inspections 1 01/01/13 Inspections 2 01/02/13 Inspections 0 01/03/13 Inspections 4 01/04/13 Inspections 0 01/05/13 Inspections 8 01/06/13
How would I do this? I am not much of a Database Developer and am new to Postgres, so I am not sure where to start and can not find anything on how to accomplish this objective.