0

Similar to this question - Postgres - how to return rows with 0 count for missing data?

I have a table to tack number of users used my service per day. I record it by timestamps and user ID. Current query -

SELECT DATE_TRUNC('day', recorded_at), COUNT(log_id) AS daily_count FROM uses_tracker 
WHERE log_id = 15183 AND recorded_at >= now() - INTERVAL '30 DAYS' GROUP BY 1

It returns me the data in this format -

date_trunc                   daily_count
2021-04-01 00:00:00.000000      2
2021-04-06 00:00:00.000000      1

Expected Format -

date_trunc                   daily_count
2021-04-01 00:00:00.000000      2
2021-04-02 00:00:00.000000      0
2021-04-03 00:00:00.000000      0
2021-04-04 00:00:00.000000      0
2021-04-05 00:00:00.000000      0
2021-04-06 00:00:00.000000      1

the data which i am getting currently is because other timestamps are not inserted in DB. How to add the missing one's while querying the data?

Prashant
  • 28
  • 2

1 Answers1

3

You can generate a series of dates for last 30 days and then left join your table on date of the series and count. Try something like

SELECT date, COUNT(ut.log_id) AS daily_count
FROM (
 SELECT date_trunc('day', dd)::date as date
 FROM generate_series
  ( now() - INTERVAL '30 DAYS'
  , now()
  , '1 day'::interval) dd
 ) dates
LEFT JOIN uses_tracker ut on ut.recorded_at::date = dates.date
GROUP BY 1
Adil Khalil
  • 2,073
  • 3
  • 21
  • 33
  • Hello thanks i did tried. But it's still not returning those Rows..just returning the one with values. Query is same as yours. Any idea on how to assign 0 if it doesn't exist? – Prashant Apr 13 '21 at 02:09
  • I did - SELECT DATE_TRUNC('day', recorded_at), COUNT(log_id) AS daily_count FROM uses_tracker ut LEFT JOIN ( SELECT date_trunc('day', dd):: date FROM generate_series ( now() - INTERVAL '30 DAYS' , now() , '1 day'::interval) dd ) as dates on dates.date = ut.recorded_at::date WHERE log_id = 15183 GROUP BY 1 Says - column dates.date does not exist – Prashant Apr 13 '21 at 05:07
  • When i do this i get - '(', ',', CROSS, EXCEPT, FETCH, FOR, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, LIMIT, NATURAL, OFFSET, ORDER, RIGHT, TABLESAMPLE, UNION, WHERE or WINDOW expected, got 'd' So i did this - SELECT d.date, COUNT(ut.log_id) AS daily_count FROM ( SELECT date_trunc('day', dd):: date FROM generate_series ( now() - INTERVAL '30 DAYS' , now() , '1 day'::interval) dd ) as d JOIN uses_tracker ut on ut.recorded_at::date = d.date GROUP BY 1 And i still get - [42703] ERROR: column d.date does not exist Position: 244 – Prashant Apr 13 '21 at 05:31
  • Didn't wanted to spam comment section so here's the link https://mystb.in/InterpretedCraftGreat.sql Still error – Prashant Apr 13 '21 at 05:45
  • alias was misspelled in the join statement. must be okay now. – Adil Khalil Apr 13 '21 at 05:50
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/231049/discussion-between-prashant-and-adil-khalil). – Prashant Apr 13 '21 at 06:15