I figured out a trick to get a count(*) to return 0 in a query in MS SQL Server.
Suppose I had a table called company like follows:
|
manager |
employee |
happy |
|
1 |
a |
n |
|
1 |
b |
n |
|
2 |
c |
y |
|
2 |
d |
n |
|
3 |
e |
y |
|
3 |
f |
y |
I wanted to get a count of happy employees grouped by manager. At first, I tried:
SELECT manager, COUNT(*) as happy_count
FROM company
WHERE (happy = ‘y’)
GROUP BY manager
But, when I ran this, I got:
|
manager |
happy_count |
|
2 |
1 |
|
3 |
2 |
What happens is that the where clause doesn’t get anything for manager 1, so there’s no count for manager 1. What I want is a count of 0 for manager 1. I didn’t want to use a temporary table or a stored procedure. I figured there’s got to some easy way to do this.
Then I thought about this:
SELECT manager, SUM(CASE happy WHEN ‘y’ THEN 1 WHEN ‘n’ THEN 0 END) AS happy_count
FROM company
GROUP BY manager
|
manager |
happy_count |
|
1 |
0 |
|
2 |
1 |
|
3 |
2 |
Bingo! The trick is to use a sum statement in conjunction with a case.