Filling in missing dates in record set from generate_series()

Question

Consider:

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.

Answer 1

This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details:

SELECT text 'Inspections'       AS data_label
     , count(i.close_case_date) AS daily_count
     , d.day                    AS date_column
FROM  (
   SELECT generate_series(timestamp '2013-01-01'
                        , timestamp '2013-01-01' + interval '1 year - 1 day'
                        , interval  '1 day')::date
   ) d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day
GROUP  BY d.day
ORDER  BY d.day;

About generating a series of dates:

• Generating time series between two dates in PostgreSQL

Minor points

• date '2013-01-01' + interval '1 year - 1 day' is better than date '2013-01-01' + 365 to also cover leap years.

• Using a cheaper subquery. No need for a CTE.

• Why count(i.reporting_id)? To just count rows in i, use count(i.close_case_date), which we already join to, so it cannot be NULL. count() only counts non-null values. If reporting_id can be NULL, you run the risk of not counting those rows.

Alternatively, move the cast, so we can use the set-returning function as table expression directly:

SELECT text 'Inspections'       AS data_label
     , count(i.close_case_date) AS daily_count
     , d.day::date              AS date_column
FROM   generate_series(timestamp '2013-01-01'
                     , timestamp '2013-01-01' + interval '1 year - 1 day'
                     , interval  '1 day') AS d(day)
LEFT   JOIN inspection i ON i.close_case_date = d.day::date
GROUP  BY d.day
ORDER  BY d.day;

Answer 2

with days as (select day::date
from generate_series(date '2013-01-01', date '2013-01-01' + 3, interval '1 

day' day) day
),
inspection as (select date '2013-01-01' AS close_case_date 
UNION ALL select date '2013-01-03')
select 
(SELECT COUNT(*) 
  FROM inspection AS i WHERE i.close_case_date = d.day) AS daily_count, 
d.day as date_column
from days d



1;"2013-01-01"
0;"2013-01-02"
1;"2013-01-03"
0;"2013-01-04"