Case Statements

Case statements are important in data analysis. That is what you and I will cover in this post. After this post, you should know the following.

Learning Objectives:

• Define what a CASE statement does.
• Describe situations where a CASE statement is useful.
• Explain the parts of CASE statement syntax.
• Use a CASE statement with correct syntax.
• Explain how to categorise, or bin, your data.

What is a Case Statement?

A case statement mimics an ‘if else’ statement found in most programming languages. It can be used in SELECT, INSERT, UPDATE and DELETE statements.

Here are a couple of ways to visualise what a case statement might look like.

CASE
WHEN C1 THEN E1
WHEN C2 THEN E2
...
ELSE [result else]
END

Looks pretty easy right? Let’s add some more complexity.

CASE input_expression
  WHEN when_expression THEN result_expression [...n]
  [ ELSE else_result_expression ]
END

Still pretty easy right?

Let’s try to work out a real problem. Here is a table of data we have.

Table of data.

And here is the code we will look at:

SELECT employeeid,
firstname,
lastname,
city,
CASE City 
  WHEN 'Calgary' THEN 'Calgary'
ELSE 'Other'
  END calgary
FROM Employees
ORDER BY LastName, FirstName;

You can see we select 4 columns and then took a look at the city column for our case statement. Then where the city column is ‘Calgary’ in the newly define ‘calgary’ column, it displays as ‘Calgary’. If it is not ‘Calgary’ then we display ‘other’ in the ‘calgary’.

When working with other types of data you could also use comparison operators to filter using your CASE statement.

This is what you would call ‘binning data’. Categorising data into more than one output is called binning. For example, if you had three categories for a variable and they were ‘small’, ‘medium’ and ‘large’. Each of these categories would be a bin and therefore requires its one ‘WHEN’ and ‘THEN’ when defining the case statement.

Here is an example:

SELECT trackid,
name, 
bytes
CASE
WHEN bytes < 300000 THEN 'small'
WHEN bytes >= 300001 AND bytes <= 500000 THEN 'medium'
WHEN bytes >= 500001 THEN 'large'
ELSE 'Other'
END bytescategory
FROM tracks;

Do let me know what you think of this post. I am still a learner myself and I would love to hear your thoughts. You are more than welcome to message me on LinkedIn or Twitter.