4

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.

11

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

SELECT 'Inspections'::text   AS data_label
     , count(i.reporting_id) 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 do you count(i.reporting_id)? To just count rows in i, use count(i.close_case_date), which we already join to.

  • This is what I needed, thanks for the pointers. Still a newbie with Postgres and Databases in general. – Mr. Concolato 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"
  • Could you elaborate a bit because that did not work? There is a syntax error here: LATERAL (SELECT COUNT(*) as daily_count – Mr. Concolato 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. – Mr. Concolato 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.