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]