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 said...

Nice one! Thanks for sharing.

Eugene said...

Awesome! Thanks.

Anonymous said...

now how about one for Oracle

Anonymous 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

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

Anonymous said...

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

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

Anonymous said...

SQL Server 2005 has the PIVOT keyword for these situations.

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