Conditional Sums in SQL Aggregate Methods

One thing that you may encounter frequently is a situation where you want to count the records in a table which are of a certain type. You want to do this as efficiently as possible, instead of running a new count query for each type. Let’s use the below table for reference.

Name	Type	Age
Buffy	Dog	10
Dusty	Dog	13
Clyde	Dog	2
Bonnie	Cat	1
Milky	Ferret	1

We have above a table of animals. How can we determine the count of each type of animal? Perhaps the most direct method that comes to mind is to run a COUNT() query for each type of animal. This would mean we have 3 queries to run, as there are 3 types of animals, and a fourth to get the results.

DECLARE @numdogs INT
DECLARE @numcats INT
DECLARE @numferrets INT
 
SELECT @numdogs = COUNT(*) FROM animals WHERE TYPE = 'Dog'
SELECT @numcats = COUNT(*) FROM animals WHERE TYPE = 'Cat'
SELECT @numferrets = COUNT(*) FROM animals WHERE TYPE = 'Ferret'
 
SELECT @numdogs AS NumDogs, @numcats AS NumCats, @numferrets AS NumFerrets

We will get results from that query like this.

NumDogs	NumCats	NumFerrets
3	1	1

A method to simplify this query and to run within a single select that I have used with success is to use a “conditional” sum in the SQL query. So rather than running COUNT(*) we will be running SUM(…). We will combine the SUM() call with a CASE statement, so that it counts correctly. This is the trick I hope to teach you, which you may not have considered before: you can place CASE … END statements inside of aggregate functions.

Throw away all of our temporary variables, the 4 Select statements involved, and let’s replace them with this single select.

SELECT 
	SUM(CASE WHEN TYPE='Dog' THEN 1 ELSE 0 END) AS NumDogs,
	SUM(CASE WHEN TYPE='Cat' THEN 1 ELSE 0 END) AS NumCats,
	SUM(CASE WHEN TYPE='Ferret' THEN 1 ELSE 0 END) AS NumFerrets
FROM 
	animals

We get these results:

NumDogs	NumCats	NumFerrets
3	1	1

Which is exactly what we got previously, but using a much simpler method. The trick was to insert a CASE … END statement inside the SUM. The case will return a 1 when the type is the type we are looking for, and a 0 otherwise. The sum will then execute to add up all of those 0s and 1s. A 0 will not add anything, so we will effectively end up counting the values which match the type we are looking for.