Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Three Ways To Display Two Counts From a Table Side By Side
From Wiki
How do you display a count of values in a column side by side? 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
--stick the count in variables
- USE PUBS
- GO
- 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
Contributed by: --SQLDenis 03:03, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Handy tricks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.