Program With Dave

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.

We have above a table of animals. How can we determine the count of each type of animal? The simplest (and most inefficient) 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.

1
2
3
4
5
6
7
8
9

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.

As you can imagine, this is terribly inefficient. A good answer in this case 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. 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.

1
2
3
4
5
6

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:

Which is exactly what we got previously, but using a much more efficient 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.

This entry was posted on Tuesday, September 29th, 2009 at 11:06 am and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.