In PostgreSQL, how to get number of event per day between a period including days without events (0-CodePudding

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.

CodePudding user response：

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