Having trouble with SQL COUNT

Question

I'm using MS SQL 2008 and I have a table of statuses (id, name) and a table of items (id, name, statusid, deleted).

I want to count the number of items with each status, and have the following query:

SELECT status.id,
ISNULL(COUNT (items.name), 0) AS 'count'
FROM status
LEFT OUTER JOIN items
ON items.statusid = status.id
GROUP BY status.id

The complication is that I want to get all the statuses, with a 0 if there are no items for the status.

The above works fine for this, but when I add in WHERE items.deleted = 0, it no longer displays the rows with a 0 in them.

Why is the WHERE clause killing my query? I suspect my method may be wrong...

Thanks :)

Answer 1

SELECT  status.id,
        COUNT (items.statusid) AS 'count'
FROM    status
LEFT OUTER JOIN
        items
ON      items.statusid = status.id
        AND items.deleted = 0
GROUP BY
        status.id

Why is the WHERE clause killing my query? I suspect my method may be wrong...

Because if there are no items with given statusid, LEFT JOIN returns a single row with all items fields set to NULL.

Your WHERE clause filters these rows out (NULL = 0 returns NULL) and they don't get into the GROUP BY.

Also note that ISNULL on COUNT is useless: COUNT never returns NULL values. It just doesn't count NULLs, so putting a column that is a part of a JOIN clause into COUNT wil do the trick.

Answer 2

The problem is that there may not be an items row to reference, as it's across the outer join; you could try recasting your WHERE clause using ISNULL:

WHERE ISNULL(items.deleted, 0) = 0