CODEWITHSUNDEEP
sqlpostgresql
Sebastiaan van den Broek
Sebastiaan van den Broek

Reputation: 6351

How to right join these series so that this query will return results where count = 0 in Postgresql SQL?

I have the following query that runs on a Postgresql database:

SELECT NULL AS fromdate,
       l.eventlevel,
       SUM(CASE
               WHEN e.id IS NULL THEN 0
               ELSE 1
           END) AS COUNT
FROM event e
RIGHT JOIN
  (SELECT generate_series(0, 3) AS eventlevel) l ON e.event_level = l.eventlevel
WHERE e.project_id = :projectId
GROUP BY l.eventlevel
ORDER BY l.eventlevel DESC

With the (trimmed) event table:

TABLE public.event
    id uuid NOT NULL,
    event_level integer NOT NULL

This is a variant for a bucketed query but with all data, hence the NULL fromdate. I'm trying to get counts from the table event and counted per event_level. But I also want the number 0 to return when there aren't any events for that particular event_level. But the current right join is not doing that job. What am I doing wrong? I also tried adding OR e.project_id IS null thinking it might be filtering out the 0 counts. Or would this work with a CROSS JOIN and if so how?

Current result:

+----------+------------+-------+
| fromdate | eventlevel | count |
+----------+------------+-------+
| null     |          3 |     1 |
+----------+------------+-------+

Desired result:

+----------+------------+-------+
| fromdate | eventlevel | count |
+----------+------------+-------+
| null     |          3 |     1 |
| null     |          2 |     0 |
| null     |          1 |     0 |
| null     |          0 |     0 |
+----------+------------+-------+

Upvotes: 2

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

I recommend avoiding RIGHT JOINs and using LEFT JOINs. They are just simpler for following the logic -- keep everything in the first table and matching rows in the subsequent ones.

Your issue is the placement of the filter -- it filters out the outer joined rows. So that needs to go into the ON clause. I would recommend:

SELECT NULL AS fromdate, gs.eventlevel,
       COUNT(e.id) as count
FROM generate_series(0, 3) gs(eventlevel) LEFT JOIN
     event e
     ON e.event_level = gs.eventlevel AND e.project_id = :projectId
GROUP BY gs.eventlevel
ORDER BY gs.eventlevel DESC;

Note the other simplifications:

  • No subquery is needed for generate_series.
  • You can use COUNT() instead of your case logic.

Upvotes: 1

jarlh
jarlh

Reputation: 44796

You have to move the e.project_id condition from the WHERE clause to the ON clause to get true RIGHT JOIN result:

           ...
           END) AS COUNT
FROM event e
RIGHT JOIN
  (SELECT generate_series(0, 3) AS eventlevel) l ON e.event_level = l.eventlevel
AND e.project_id = :projectId
...

Upvotes: 2

Related Questions