Is it possible to COUNT the CONDITION and including NULL as well as the other values as 0?-CodePudding

I have a problem. It includes the condition that to COUNT the rows where its status = 1 (GROUP BY name).

However, the result should include the rows WHERE those are not = 1 and NULL. And they are counted as 0.

I have tried cte, CASE WHEN, WHERE status = 1 or status IS NULL. It does include null as 0, but there are name containing 1 and 0 or only containing 0.

If I use WHERE status IS NULL OR status=1, the name with status 0 is not counted.

If I use CASE WHEN status IS NULL THEN 0

          WHEN status IS 0 THEN 0

          WHEN status = 1 THEN COUNT(DISTINCT name)

Then the name containing 1 AND 0 will be counted as 0.

TABLE:

INSERT INTO students (name, student_id, exercise_id, status) VALUES (Uolevi, 1, 1, 0), (Uolevi, 1, 1, 0), (Uolevi, 1, 1, 1), (Uolevi, 1, 2, 0), (Uolevi, 1, 2, 0), (Uolevi, 1, 2, 1), (Maija , 2, 1, 1), (Maija , 2, 2, 1), (Maija , 2, 2, 1), (Maija , 2, 2, 1), (Maija , 2, 3, 0), (Juuso , 3, 1, 0), (Juuso , 3, 2, 0), (Juuso , 3, 3, 0), (Miiko , NULL, NULL, NULL);

CodePudding user response：

SELECT s.name, COUNT(DISTINCT exercise_id) FROM students s LEFT JOIN transmissions t ON s.id=t.student_id AND state = 1 group by student_id

CodePudding user response：

EDITED since you specified your expected result. You can add a FILTER clause to the aggregate function COUNT() to only count the rows matched by the filter:

SELECT name, 
       COUNT(DISTINCT exercise_id) FILTER (WHERE status=1) as exercise_count
FROM students
GROUP BY student_id
ORDER BY exercise_count DESC

Here is a fiddle with the values you provided

My query doesn't take into account the table transmissions you used in your answer because you didn't specify it's structure or what it does contain, but you can adapt the answer to your real needs.