Last time we used
GROUP BY and
HAVING to learn how to group and condition data in a particular column. Now let’s use a query called
CASE to figure out how to assign values to data that corresponds to a specific condition.
📌CASE: set a condition and return values depending on that condition
#Table of Contents
Query 1. #CASE
SELECT customer_id, active, CASE WHEN active=1 THEN ‘ACTIVE’ ELSE ‘INACTIVE’ END as ‘Status’ FROM customer;
Query 2. #CASE with ranges
SELECT title, rental_duration, CASE WHEN rental_duration <=3 THEN ‘SHORT’ WHEN rental_duration >=4 THEN ‘MEDIUM’ WHEN rental_duration >=6 THEN ‘LONG’ ELSE NULL END as ‘Duration’ FROM film;
Q1. In the customer table, specify which customers are active and inactive.
CASE WHEN (condition1) THEN (specified value1)
WHEN (condition2…) THEN (specified value2…)
ELSE (specified value)
END AS (new column name)
Let’s review what information we have in the customer table. You can do this by using your Object Panel (double click on tables and then double click on customer) or by running this query:
SELECT * FROM customer;
We’ve discussed a method on how to pinpoint exactly how many customers are active. Now let’s learn an additional way to filter this information with the
The information in the active column has two results, 0 and 1. If the customers are active, it’s marked with a 1. If they’re not active, they have a 0. The first step is to designate what we want to see, so let’s specify that by writing customer_id, first_name, last_name, and active after the
SELECT query. Like this:
SELECT customer_id, first_name, last_name, active FROM customer;
This query will give you a list of all the customers with their active information. Figuring out the 1s and 0s can get a little bothersome, so let’s change that information into text. We’re going to create a new column and rename the values inside so that instead of numbers, we can see Active and Inactive. No, we won’t use
We’re going to use
First, let's set some precedents. We will tell SQL that if the information in the active column is a 1 (
WHEN active=1), we want to see the word Active (
THEN ‘ACTIVE’). If the information is not a 1 (
ELSE), we want to see Inactive (
‘INACTIVE’) . Here’s how:
SELECT customer_id, first_name, last_name, active, CASE WHEN active=1 THEN ‘ACTIVE’ ELSE ‘INACTIVE’ END FROM customer;
⚠️Remember to put a comma (
CASE WHEN here! And we need to let SQL know the conditions are finished by writing
We have a cleaner table now, and it’s easier to read this information. But look at that title for the new column… that doesn’t look very good. We can clean it up in the same
CASE query by adding that we want to finish up the new column with the name Status (
END as ‘Status’). Try this:
SELECT customer_id, first_name, last_name, active, CASE WHEN active=1 THEN ‘ACTIVE’ ELSE ‘INACTIVE’ END as ‘Status’ FROM customer;
💡 Using the
CASE query, we learned how to tell SQL that if condition 1 applies (customer has 1 in the active column), then please return the information as specified value 2 (tell us ACTIVE).
Q2. In the film table, let’s designate a value based on the rental duration.
Going back to the film table, use the same structure we just learned to specify the rental period. Let’s start by checking out the rental duration of the movies we have in the film table.
SELECT title, rental_duration FROM film;
As you can see, the duration is displayed numerically (6 days, 3 days, 7 days, etc). We can change this so that the values are displayed as text. Let’s set some parameters first. Let’s break down the rental periods into short, medium, and long.
Short: Rental period is less than(
<) or equal to (
=) 3 days. (
Medium: Rental period is more than 3 days but less than 6 (
Long: Rental period is more than (
>) or equal to (
=) 6 days. (
If they don’t fit any of these categories, they will be considered Null (
ELSE NULL). And we’re going to name our new column Duration (
END as ‘Duration’)
Here’s the full code:
SELECT title, rental_duration, CASE WHEN rental_duration <=3 THEN ‘SHORT’ WHEN rental_duration > 3 AND rental_duration < 6 THEN ‘MEDIUM’ WHEN rental_duration >=6 THEN ‘LONG’ ELSE NULL END as ‘Duration’ FROM film;
💡 You can take this one more step and plug in
ORDER BY rental_duration !
# Practice Time
📰 Using the payment table, organize the data so customers that owe $9.99 and above are considered ‘High’, $4.99 and above is considered ‘Medium’ and $3.99 and below is considered ‘Low’.
Here's the answer!
SELECT customer_id, amount, CASE WHEN amount >= 9.99 THEN 'High' WHEN amount <9.99 AND amount >3.99 THEN 'Medium' WHEN amount <= 3.99 THEN 'Low' ELSE NULL END as 'Payment Amount' FROM payment;
💡 You can take this one more step and plug in
ORDER BY amount!