How to do calculations and expressions in T-SQL
Part four of a five-part series of blogs

You can use SQL to do everything from simple arithmetic through to complicated functions - this blog gives you the low-down!

  1. Formulae, Expressions and Calculations in SQL
  2. Arithmetic and Numerical Functions
  3. Working with text (including string functions)
  4. CASE WHEN statement (this blog)
  5. 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:

List of actors with age bands

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:

Films, oscars and text verdict

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 (!):

Actors by age and gender

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:

Number of films for length bands

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:

  1. You might mistype something the second time round, and be grouping by and displaying two different expressions.
  2. It's a lot of typing!
  3. 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!

This blog has 0 threads Add post