Group by count zero rows not displaying

Question

I want to count the amount of rows of every

componistId

. When I run the following SQL statement it works fine:

SELECT C.componistId, COUNT(*)

FROM Componist C LEFT JOIN Stuk S ON S.componistId = C.componistId

GROUP BY C.componistId

Now I want only the rows where

stukNrOrigineel

is null

SELECT C.componistId, COUNT(*)

FROM Componist C LEFT JOIN Stuk S ON S.componistId = C.componistId

WHERE S.stuknrOrigineel IS NULL

GROUP BY C.componistId

But when I do this, all the rows with a result of 0 disappear. Only the rows with at least 1 row are displayed. How can I make this work?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You need to include the condition in the on clause:

SELECT C.componistId, COUNT(C.componistId)
FROM Componist C LEFT JOIN
     Stuk S
     ON S.componistId = C.componistId AND
        S.stuknrOrigineel IS NULL
GROUP BY C.componistId;

Note: I changed the COUNT() to count from the second table. This is normally what you want when combining LEFT JOIN with COUNT().

On some databases, I think the above might not quite work as expected (the question is whether the condition on S is evaluated before or after the LEFT JOIN). This should always work:

SELECT C.componistId, COUNT(C.componistId)
FROM Componist C LEFT JOIN
     (SELECT S.* FROM Stuk S WHERE S.stuknrOrigineel IS NULL
     ) s
     ON S.componistId = C.componistId AND
GROUP BY C.componistId;

Another generic solution is move the condition into the aggregation function:

SELECT C.componistId, 
       SUM(CASE WHEN S.stuknrOrigineel IS NULL THEN 1 ELSE 0 END) 
FROM Componist C LEFT JOIN
     Stuk S
     ON S.componistId = C.componistId 
GROUP BY C.componistId;

Group by count zero rows not displaying

Sign up