Blog

Search | Archive | Categories
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.

Posted: 2002-12-04 11:52:23


<< Larry King Interview with Bono from U2Confederate Flag >>

RSS/XML