Author: Steven Neiland
Published:

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.

Table Structure

Column Name Column Type
name varchar(20)
classroom varchar(20)
age int
gender char(1)

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.

SELECT
      classname
      , SUM(age) AS totalage
FROM
      children

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.

SELECT
      classname
      , SUM(CASE WHEN gender = 'M' THEN age ELSE 0 END) AS boysage
      , SUM(CASE WHEN gender = 'F' THEN age ELSE 0 END) AS girlsage
FROM
      children

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.

  1. We change the SUM() to a COUNT()
  2. 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:

SELECT
      classname
      , COUNT(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS boyscount
      , COUNT(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS girlscount
FROM
      children

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.

Related Blog Postings

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub