BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
You can use SQL to do everything from simple arithmetic through to complicated functions - this blog gives you the low-down!
- Formulae, Expressions and Calculations in SQL
- Arithmetic and Numerical Functions
- Working with text (including string functions)
- CASE WHEN statement (this blog)
- Dealing with nulls - ISNULL, COALESCE and CASE
This blog is part of our full SQL online tutorial. You can learn how to write SQL on a Wise Owl SQL classroom course if you're in the UK.
Posted by Andy Brown on 11 December 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
CASE WHEN statement
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:
-- show actor ages as of 25/12/2013
SELECT
ActorName,
CONVERT(char(10),ActorDob,103) AS 'When born',
-- divide actors into age bands by date of birth
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,
-- show Oscars as text
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:
-- divide actors up into male/female, and by age
SELECT
ActorName,
ActorGender,
CONVERT(char(10),ActorDob,103) AS Dob,
-- male or female
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:
-- show number of films by length category
SELECT
-- calculate the category
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
-- what goes here?
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:
-- show number of films by length category
SELECT
-- calculate the category
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
-- create a view to show the films with length bands
SELECT
-- calculate the category
CASE
WHEN FilmRunTimeMinutes < 120="">THEN 'Short'
WHEN FilmRunTimeMinutes < 180="">THEN 'Medium'
ELSE 'Long'
END AS Category,
FilmName
FROM
tblFilm
GO
-- now create a query based on this view
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:
-- variable to hold message to display
DECLARE @Message varchar(MAX)
-- work out what should be displayed
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 this out
PRINT @Message
This uses variables, but hopefully it will be reasonably obvious what the query is doing!