Sjoerd Sjoerd - 4 years ago 92
SQL Question

Group by count zero rows not displaying

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?

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download