Google
Google Interview Questions | SQL Server Software | Microsoft SQL Server Books | Denis Gobo's personal blog | Pro SQL server 2005 (Apress)
Top 10 Articles | Add Yourself To My Frappr Map | Ken Henderson Interview | Louis Davidson Interview

Friday, December 15, 2006

Three Ways To Display Two Counts From a Table Side By Side

This question came up on tek-tips.

I want to count the CA and the UT states from the authors table and output this in different columns named CaCount and UtCount

CaCount UtCount
----------- -----------
15 2


How do you do such a thing? To display the following is very easy

state StateCount
----- -----------
CA 15
UT 2

You just do a count and group by

SELECT state,COUNT(*) AS StateCount
FROM authors
WHERE state in ('CA', 'UT')
GROUP BY state


How do we display them side by side? We can do this 3 different ways
1 stick the count in variables
2 run the count twice a s a separate query
3 sum with case

USE PUBS
GO

--stick the count in variables
DECLARE @UtCount INT
DECLARE @CaCount INT

SELECT @UtCount = COUNT(*) FROM authors WHERE state ='UT'
SELECT @CaCount = COUNT(*) FROM authors WHERE state ='CA'

SELECT @CaCount AS CaCount,@UtCount AS UtCount

--run the count twice as a separate query
SELECT (SELECT COUNT(*) FROM authors
WHERE state ='CA') AS CaCount,
(SELECT COUNT(*) FROM authors WHERE state ='UT') AS UtCount


--sum with case
SELECT SUM(CASE state WHEN 'CA' THEN 1 ELSE 0 END) AS CaCount,
SUM(CASE state WHEN 'UT' THEN 1 ELSE 0 END) AS UtCount
FROM authors


If you look at the execution plan then you will see that the best option is the third one (sum with case). The third option only queries the table once instead of twice like the other two solutions and will thus be twice as fast
So keep that in mind when you have to do such a query next time

[edit]
Oracle Version
jbjornson left the Oracle version in a comment; I decided to add it to the post. I don't know about the syntax coloring on Oracle/TOAD so you are getting a black and white only ;-(

--run the count twice as a separate query
SELECT CA.caCount, UT.UtCount
FROM (SELECT COUNT(*) as CaCount FROM authors) WHERE state ='CA') CA,
(SELECT COUNT(*) FROM authors WHERE state ='UT') UT


--sum with case
SELECT SUM(decode(state, 'CA', 1, 0)) AS CaCount,
SUM(decode(state, 'UT', 1, 0)) AS UtCount
FROM authors
[/edit]

8 Comments:

Anonymous David said...

Nice one! Thanks for sharing.

2:02 PM  
Blogger Eugene said...

Awesome! Thanks.

2:56 PM  
Anonymous matelot said...

now how about one for Oracle

3:38 PM  
Anonymous jbjornson said...

for oracle:

--run the count twice as a separate query
SELECT CA.caCount, UT.UtCount
FROM (SELECT COUNT(*) as CaCount FROM authors) WHERE state ='CA') CA,
(SELECT COUNT(*) FROM authors WHERE state ='UT') UT


--sum with case
SELECT SUM(decode(state, 'CA', 1, 0)) AS CaCount,
SUM(decode(state, 'UT', 1, 0)) AS UtCount
FROM authors

4:24 AM  
Anonymous Anonymous said...

oracle8i and up has proper case statements. in fact, decode has been deprecated.

aircoding here.. soz

select sum(case when state = 'CA' then 1 else 0 end) cacount,
sum(case when state = 'UT' then 1 else 0 end) utcount
from authors

1:59 PM  
Anonymous Mark Brackett said...

I'd think COUNT would be better than SUM performance wise.

So, COUNT(CASE State WHEN 'CA' THEN 1 END) as CaCount

8:33 AM  
Anonymous Anonymous said...

SQL Server 2005 has the PIVOT keyword for these situations.

7:39 PM  
Blogger Mohit Nayyar said...

We can make use of PIVOT in SQL Server 2005

SELECT [CA] as CALIFORNIA, [UT] as UTAH
FROM
(SELECT au_id, state
FROM authors) p
PIVOT
(
COUNT (au_id) FOR state IN ([CA], [UT])
) AS pvt

- Mohit Nayyar

10:14 AM  

Post a Comment

<< Home