Using CASE Statements in Your Select

Let's say that you have a table of data that you want to group together.  Your data might be one of the following:

• You want to give a category or title to a list of ranges (using int, numeric, float, etc.)
• You want to group values that aren't necessarily in the same range (varchar, etc)
• You want to give more description to a single value or list of values
• You rely on more than one condition to fall under a particular category

There are a few ways that you could approach grabbing this data.  You could write separate queries, just grabbing the data you want grouped together, and then tying all data together using UNION ALL.  That query would look something like this:

SELECT 'Less than $10' as Category, count(*)

from Sales

where SalesAmount < 10

UNION ALL

SELECT 'Between $10 and $50' as Category, count(*)

from Sales

where SalesAmount between 10 and 50

UNION ALL

SELECT 'Over $50' as Category, count(*)

from Sales

where SalesAmount > 50

This certainly gets the job done, but leaves too much room for error.  Your other choice is to write a CASE statement in your select statement (which would need to be accompanied by using the same CASE statement in a group by).  Accomplishing the same output, you could do something like this:

SELECT Category = CASE

WHEN SalesAmount < 10 then 'Less Than $10'

WHEN SalesAmount between 10 and 50 then 'Between $10 and $50'

WHEN SalesAmount > 50 then 'Over $50'

END,

count(*)

from Sales
group by CASE
WHEN SalesAmount < 10 then 'Less Than $10'
WHEN SalesAmount between 10 and 50 then 'Between $10 and $50'

WHEN SalesAmount > 50 then 'Over $50'

END

You can also use the CASE statement to provide more than one condition:

SELECT Category = CASE
WHEN SalesAmount < 9 or SalesAmount > 99 then 'Small and Big Sales'
WHEN SalesAmount between 10 and 50 then 'Middle Sales'
END,
count(*)
from Sales
group by CASE
WHEN SalesAmount < 9 or SalesAmount > 99 then 'Small and Big Sales'
WHEN SalesAmount between 10 and 50 then 'Middle Sales'
END

Go here to read more on SQL CASE statements:

SQL CASE