Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.
In continuation of my previous post on filtering aggregate columns I thought today I would demonstrate two simple ways to do conditional data aggregation.
Problem 1: Get the total age of children by gender per class
In this totally made up example we want to get the sum total age of all children in a school broken down by gender and classroom.
|Column Name||Column Type|
Step 1: Total ages by class
Obviously the first step is to get the sum of all childrens age broken down by class from the children table.
, SUM(age) AS totalage
Step 2: Conditionally sum the ages
To further break this down by gender all we need to do is put a CASE statement inside the SUM() function to return either the age of a student if they match a particular gender in the gender column or zero if they don't.
-- Sum the ages of all boys
SUM(CASE WHEN gender = 'M' THEN age ELSE 0 END)
We repeat this for girls and we get this.
, SUM(CASE WHEN gender = 'M' THEN age ELSE 0 END) AS boysage
, SUM(CASE WHEN gender = 'F' THEN age ELSE 0 END) AS girlsage
Pretty simple right. Now lets change this to a simple count.
Problem 2: Conditionally count the children
In order to count the number of boys and girls broken down by class we only need to make two small changes.
- We change the SUM() to a COUNT()
- Then we change the 'age' to a 1
-- Count the boys
COUNT(CASE WHEN gender = 'M' THEN 1 ELSE 0 END)
This gives us this:
, COUNT(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS boyscount
, COUNT(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS girlscount
While these are very simple examples these techniques can be used in much more complex ways and can greatly improve you code performance if used correctly.