SQL Group By With Count Zero

Question

I have the following query which is not working for my requirement. I need the count zero also returned in the results. I am looking for DocIDs with count zero for the condition specified but those DocIDs are not even showing up in the result.

SELECT DocID, Count(*) AS ArchivedCount FROM Doc WHERE Status <> 'Archived' GROUP BY DocID ORDER BY DocID

So you want to count DocIDs that aren't in the table "Doc"? It's possible to do that. But if those DocIDs aren't in "Doc", what table are they in? — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Mar 21, 2015 at 22:06
Are you sure that DocID is really zero? It might be NULL. In that case you need to use COUNT(COALESCE(DocID, 0)) — Maciej Los
– Maciej Los, Commented Mar 21, 2015 at 22:09

Sergey Kalinichenko · Accepted Answer · 2015-03-21 22:06:36Z

Since you need to use all DocIDs, you should use SUM in place of COUNT, and move the condition into a CASE statement inside the SUM, like this:

SELECT DocID , SUM(CASE WHEN Status <> 'Archived' THEN 1 ELSE 0 END) AS ArchivedCount FROM Doc GROUP BY DocID ORDER BY DocID

Now the result is going to contain all DocIDs, even the ones with the Status different from 'Archived'.

@MaciejLos But that's what OP is looking for - DocIDs with the count of zero.

Collectives™ on Stack Overflow

SQL Group By With Count Zero

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related