9

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.

0
15

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:

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;
1
  • 1
    This is what I needed, thanks for the pointers. Still a newbie with Postgres and Databases in general. Jul 28 '14 at 21:54
1
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"
4
  • Could you elaborate a bit because that did not work? There is a syntax error here: LATERAL (SELECT COUNT(*) as daily_count Jul 25 '14 at 18:41
  • @Mr.Concolato The previous version worked on 9.3. Fixed.
    – A-K
    Jul 25 '14 at 18:55
  • Not sure if i am missing something here, but your query alternates the count between 1 and 0? I need it to generate a count for every day going back 365 days. If there is no count found for any day, then it should fill in that day with a 0. Not just alternate between 1 and 0. Thank you for your effort though. Jul 25 '14 at 19:00
  • to get more than 1 as count, add more test data.
    – A-K
    Jul 25 '14 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.