The versatile CASE WHEN ... END clause is the answer to many SQL questions!
Searched CASE syntax
The basic syntax of this type of CASE statement is as follows:
CASE
WHEN condition 1 is true THEN outcome 1
WHEN condition 2 is true THEN outcome 2
...
ELSE outcome n
END AS 'Column alias'
The ELSE clause is optional. As for the Excel IF function, processing will stop as soon as a condition is true.
Example of a searched CASE statement - age bands
Supposing you want to divide actors into old, middle-aged and young ones as of Christmas Day 2013 (some people might disagree with the categorisation in this example!). Here's how you could do this:
SELECT
ActorName,
CONVERT(char(10),ActorDob,103) AS 'When born',
CASE
WHEN DateDiff(year,ActorDob,'20131225') < 35="">THEN 'Young'
WHEN DateDiff(year,ActorDob,'20131225') < 60="">THEN 'Middle-aged'
ELSE 'Old'
END AS 'Age category'
FROM
tblActor
Here's some of the actors this query might return:
Only Kirsten Dunst squeaks into our Young category!
Simple CASE statements
The syntax for this type of CASE statement is very similar:
CASE Expression
WHEN value1 THEN outcome 1
WHEN value2 THEN outcome 2
...
ELSE outcome n
END
The difference is that you put the thing you're evaluating after the CASE keyword. Although this looks easier, it's actually less useful (it couldn't, for example, be used in the example above, as comparisons are involved).
Here is an example SQL statement to display the number of Oscars won by films as text:
SELECT
FilmName,
FilmOscarWins,
CASE FilmOscarWins
WHEN 0 THEN 'Not a winner'
WHEN 1 THEN 'Single Oscar'
WHEN 2 THEN 'Double'
ELSE 'Lots'
END AS Oscars
FROM
tblFilm
This would produce the following output:
The number of Oscars is shown as text too.
Nesting CASE statements
You can nest CASE statements inside each other, although it's vital that you indent your SQL correctly to make the result comprehensible:
SELECT
ActorName,
ActorGender,
CONVERT(char(10),ActorDob,103) AS Dob,
CASE ActorGender
WHEN 'Male' THEN
CASE
WHEN Year(ActorDob) < 1980="">THEN 'Man'
ELSE 'Boy'
END
WHEN 'Female' THEN
CASE
WHEN Year(ActorDob) < 1980="">THEN 'Woman'
ELSE 'Girl'
END
ELSE 'Other'
END AS Category
FROM
tblActor
This query would divide the actors up into men, women, boys (!) and girls (!):
The results of running the query above (we don't have any young males visible).
Using CASE statements in GROUP BY clauses
Suppose that you want to group films into the following length bands:
Length of film | Band |
|---|
Up to 2 hours | Short |
2 to 3 hours | Medium |
3 or more hours | Yawn-inducing |
Here's what the answer should look like:
The number of short, medium and long films.
To get this to work, you would like to do this:
SELECT
CASE
WHEN FilmRunTimeMinutes < 120="">THEN 'Short'
WHEN FilmRunTimeMinutes < 180="">THEN 'Medium'
ELSE 'Long'
END AS Category,
COUNT(*) AS 'Number of films'
FROM
tblFilm
GROUP BY
Category
The problem is that you can't group by the Category column, because it's a calculated column (and you can only use these in the ORDER BY clause of a SQL statement, not the WHERE or GROUP BY clauses).
I can think of 3 ways to solve this. The first (and most obvious) is just to repeat the CASE statement:
SELECT
CASE
WHEN FilmRunTimeMinutes < 120="">THEN 'Short'
WHEN FilmRunTimeMinutes < 180="">THEN 'Medium'
ELSE 'Long'
END AS Category,
COUNT(*) AS 'Number of films'
FROM
tblFilm
GROUP BY
CASE
WHEN FilmRunTimeMinutes < 120="">THEN 'Short'
WHEN FilmRunTimeMinutes < 180="">THEN 'Medium'
ELSE 'Long'
END
The obvious disadvantages to this are:
You might mistype something the second time round, and be grouping by and displaying two different expressions.
It's a lot of typing!
It makes the resulting SQL look hard to read.
The other two methods are as follows:
Method | Notes |
|---|
Create a function | Create a scalar user-defined function (say called fnRunTimes), and use this in both the SELECT and the GROUP BY clauses of the query). |
Create a view | Create a view (say called vwFilmRunTimes) to list out fllms with their length categories, and then create a query based on this view to group the films by category. |
Example SQL for this second method is as follows:
CREATE VIEW vwFilmRunTimes AS
SELECT
CASE
WHEN FilmRunTimeMinutes < 120="">THEN 'Short'
WHEN FilmRunTimeMinutes < 180="">THEN 'Medium'
ELSE 'Long'
END AS Category,
FilmName
FROM
tblFilm
GO
SELECT
Category,
COUNT(*) AS 'Number of films'
FROM
vwFilmRunTimes
GROUP BY
Category
Using CASE statements to control program logic
You can't do this, but there is a way round it sometimes. To explain what I mean by this, consider this example query to print out a message according to how many short films there are in a table:
IF EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes <>
PRINT 'There are some really short films'
ELSE
IF EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes <>
PRINT 'There are some shortish films'
ELSE
PRINT 'No short films found'
This uses subqueries, but hopefully it's reasonably clear what's going on. This nested IF condition is hard to read, and will get harder as we add more possible conditions. It would be much easier to read and write using CASE, but the following won't work:
CASE
WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes <>
THEN PRINT 'There are some really short films'
WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes <>
THEN PRINT 'There are some shortish films'
ELSE
PRINT 'No short films found'
END
This is because you can't use CASE to control program flow. However, you can use it to calculate expressions, leading to the following work-round:
DECLARE @Message varchar(MAX)
SET @Message =
CASE
WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes <>
THEN 'There are some really short films'
WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes <>
THEN 'There are some shortish films'
ELSE
'No short films found'
END
PRINT @Message
This uses variables, but hopefully it will be reasonably obvious what the query is doing!
