Multiple Counts in One Query

Have you have been tasked with pulling multiple counts from the same table? Maybe you need to find how many records have a value for a column and how many are NULL. Or maybe you need to see how many records are true and how many are false.

It’s simple enough to run a query to count one set of criteria, run another query for the second set of criteria, and combine them when sending your results. Did you know you can get multiple counts with one query?

Here’s an example of how using COUNT and CASE can speed up your day.

Every Vote Matters

We’ll use the StackOverflow2013 database for this example. Let’s say someone comes to you asking to have the counts of users that have and have not upvoted posts. There’s nothing wrong with running this query to get the count where there are no upvotes:

SELECT COUNT(*) as 'No_UpVotes'
FROM Users
WHERE UpVotes = 0;
GO

And this query to get a count where there have been upvotes:

SELECT COUNT(*) as 'UpVotes'
FROM Users
WHERE UpVotes != 0;
GO

These aren’t huge queries and results are returned in about a second. Each has the same amount of 30046 logical reads:

What if we want to cut our work effort in half? We can do the counts in one query by using CASE in our COUNT:

SELECT COUNT(CASE 
			WHEN UpVotes = 0
				THEN 1
			END) AS 'No_UpVotes'
	,COUNT(CASE 
			WHEN UpVotes != 0
				THEN 1
			END) AS 'UpVotes'
FROM Users;
GO

Instead of reading the table twice, we only need to read it once and can return one set of results. This query also has the same 30046 logical reads.

Take it Easy

I’m guessing this is probably not something you’ll run into very often but using multiple counts in one query can be useful in the right situations. When a query is more “get me the data once and move on” like the example above, combining them isn’t a huge impact. If this was part of a stored procedure running a significant amount of times per day, per hour, etc. then you could see some benefits.

Thanks for reading!