I need to find the way to get the number of events that happens in every day between a period, including days in which no event occurs, getting that day a 0 value.
I try this query:
SELECT f.day, COALESCE(count(e.*), 0) account
FROM (SELECT d::date day
FROM generate_series('2023-01-19'::date - '39 day'::interval, '2023-01-19', '1 day'::interval) d) f
LEFT JOIN events e ON f.day::date = e.event_date::date
WHERE e.event_date BETWEEN ('2023-01-19'::date - interval '39 days') AND '2023-01-19'
GROUP BY f.day
ORDER BY f.day desc
But, i am not getting 0 values. The period is for 40 days, but only get 31 records (9 days with no events at all doesn't appears).
I see this, Postgres, Count entries per day including days with none
but somehow seems to be like my query. But my query is not working...
Thanks for any idea!
Edit: solution The only way that i am getting what i want is getting rid of the WHERE clause. My real query has some more filters aplied.
WHERE e.state = 2 AND e.other_field IS NOT NULL
Following both great answer from Adrian or Edouard, i get the same result (but 1. they do not have all de information needed, WHERE was not complete in the problem presented, and 2. they really give me a path to find the answer). The thing that works, is move that filter to the join (avoiding re filtering by date).
LEFT JOIN events e ON f.day::date = e.event_date::date AND e.state = 2 AND e.other_field IS NOT NULL
Then i get what i want.
your issue comes from your WHERE clause to be replaced by :
WHERE e.event_date BETWEEN ('2023-01-19'::date - interval '39 days') AND '2023-01-19' :: date
OR e.event_date IS NULL
or simply to be cancelled as stated by @Adrian because it is redundant with the LEFT JOIN with the generate_series in the FROM clause.
SELECT f.date, count(e.*)
FROM generate_series('2023-01-19'::date - '39 day'::interval, '2023-01-19', '1 day'::interval) AS f(date)
LEFT JOIN events AS e
ON e.event_date = f.date :: date
GROUP BY f.date
ORDER BY f.date DESC
see dbfiddle