Back to articles list Articles Cookbook
10 minutes read

How to Use CASE in SQL

If you need to evaluate multiple conditional statements, the SQL CASE statement will do the job. Here’s what you need to know to use CASE like a pro.

Why is CASE so important in SQL? If you’re analyzing or manipulating data, you’ll often want to define rules based on certain conditions, e.g. if an employee’s performance is above average, give them a 10% raise; if it is outstanding, give them a 15% raise; otherwise, give them a 5% raise.

To handle situations where you need to evaluate many conditional statements together and return results depending on which statement is true, SQL offers the CASE statement.

The SQL CASE statement is one of the most useful conditional constructs available and has a lot of applications for analyzing data with SQL.

So, if you want to learn about the practical applications of the CASE statement and different ways to use it, this article is for you.

What Is the CASE Statement?

In SQL, the CASE statement returns results based on evaluation of certain conditions. It is quite versatile and can be used in different constructs. For instance, you can use it to display values, order sort results, or filter records. It evaluates stated conditions and returns the result for the first statement that evaluates to true.

Before I go into details on how CASE works, take a look at the syntax of the CASE statement:

CASE
WHEN <condition> THEN <value>,
WHEN <other condition> THEN <value>
ELSE <value>
END AS <column name>

Let’s look at a practical example of a simple CASE statement.

Here is the order_summary table:

order_idcustomer_idcustomer_namequantityorder_valueshipping_fee
A1231221Emily52059
A1243213Javier223000
A1253213Javier12320000
A1263213Javier1016000

Now say you are an analyst in an ecommerce firm. You want to analyze orders based on their order value and divide them into buckets (very low, low, medium, high, and very high) according to their order value.

The CASE statement can help you achieve this. Here’s the query you’d write:

SELECT  order_id,
	  CASE 
	  WHEN order_value <= 50 THEN 'Very Low'
  WHEN order_value > 50 AND order_value <= 200 THEN 'Low'
  WHEN order_value > 200 AND order_value <= 500 THEN 'Medium'
  WHEN order_value > 500 AND order_value <= 1000 THEN 'High'
  ELSE 'Very High' 
  END AS order_category
FROM    order_summary;

And here are the results you’d get:

order_idorder_category
A123Medium
A124Medium
A125Very High
A126High

Alternatively, you can also use the query given below:

SELECT order_id,
 CASE
 WHEN order_value <= 50 THEN 'Very Low'
 WHEN order_value <= 200 THEN 'Low'
 WHEN order_value <= 500 THEN 'Medium'
 WHEN order_value <= 1000 THEN 'High'
 ELSE 'Very High'
END AS order_category
FROM order_summary;

This will give you exactly the same result, as CASE stops evaluating a value once it meets the criteria in WHEN.

Now, let me break down these queries.

The first keyword is SELECT, which specifies the columns you want to return. In our case, these columns were order_id and order_category, which we used as an alias for the CASE statement (CASE...END AS order_category).

The CASE statement begins with the keyword CASE. It is followed by the keyword WHEN, after which we specify a condition to evaluate (order_value <= 50). This is immediately followed by THEN and the return value if the condition is true (‘Very Low’).

For example, take the first statement:

CASE WHEN order_value <= 50 THEN 'Very Low'

In this statement, when the order value is less than or equal to $50, ‘Very Low’ is returned as a value in the order_category column. In other words, we classify all the orders with value less than $50 or equal to $50 in the “Very Low” category.

If this condition isn’t true (the value is over $50), the query checks to see if the value is over $200. If it is under $200 but over $50, then “Low” is returned as the value in the order_category column. If the value is over $200, the query skips to the next WHEN clause, and so on.

If none of the conditions evaluates to true, then the value specified in ELSE is returned. Thus, the CASE statement adds logic to your SELECT statement.

If you are new to SQL and want to understand how to write these kinds of queries, I recommend the SQL from A to Z track from LearnSQL.com. It starts with the basics of SQL and databases, and then guides you all the way up to more sophisticated queries and functions. It’s a great way to get started on your SQL journey.

If you’re analyzing a lot of orders, aggregation would come in handy on queries like these. Aggregation means grouping together similar records and then using a metric based on the grouped values to understand the features of that group. In SQL, the GROUP BY clause is your entry into the world of aggregate statistics. (For a more detailed understanding of GROUP BY, check out this article.)

For now, let’s just see how GROUP BY and CASE work together. Here’s an updated version of our previous query:

SELECT  CASE 
	  WHEN order_value <= 50 THEN 'Very Low'
  WHEN order_value > 50 AND order_value <= 200 THEN 'Low'
  WHEN order_value > 200 AND order_value <= 500 THEN 'Medium'
  WHEN order_value > 500 AND order_value <= 1000 THEN 'High'
  ELSE 'Very High' 
  END AS order_category,
  COUNT(order_id)
FROM    order_summary
GROUP BY 1;

And the new output:

order_categoryCOUNT(order_id)
High1
Medium2
Very High1

Here, we use COUNT as the aggregate function. This is how it works. The GROUP BY clause aggregates all the records by the values returned in the first column of the SELECT. In our case, this is order_category.

Then, for each different value of order_category, COUNT(order_id) will calculate the total number of orders belonging to the corresponding category. The CASE statement helps decide which category to assign for each order. In our data, we have a total of 1 order in the ‘High’ category (order_value between 500 and 1000), 2 orders in ‘Medium’ (order_value between 200 and 500) and 1 orderin the ‘Very High’ category (order_value greater than 1000).

In all the above examples, the CASE statement has been used in the SELECT part of the query. However, this clause is quite versatile and can be used for returning condition-based results in other parts of the query.

So now that you have an idea of what the CASE statement is, let’s see some other ways to use it.

Using CASE in the ORDER BY Clause

The ORDER BY clause is used to sort query results in a given order. For instance, you might want to sort the number of orders placed by each customer on the basis of customer_name. Here’s the query you’d write:

SELECT   customer_name,
   COUNT(order_id)
FROM 	   order_summary
GROUP BY customer_name
ORDER BY customer_name;

And the output is:

customer_nameCOUNT(order_id)
Emily1
Javier3

Here, the query sorts the results in ascending alphabetical order (because you’re ordering by a text value). Unless you specify otherwise, ORDER BY will always use ascending (i.e. A-Z, 1-10) order. You can place the DESC keyword after the column name clause to sort the results in descending (Z-A, 10-1) order: ORDER BY customer_name DESC.

Suppose you want to sort records by order_id in ascending order. However, you want to show orders of over 120 items first. In other words, you will first sort by item quantity (wherever the quantity is greater than 120) and then by order ID. This will require a conditional evaluation in the ORDER BY clause:

Query:
SELECT
  customer_name,
  order_id,
  order_value,
  quantity
FROM
  order_summary
ORDER BY
  CASE WHEN quantity > 120 THEN quantity END, order_id;

Here is the output:

customer_nameorder_idorder_valuequantity
JavierA1252000123
EmilyA1232055
JavierA12430022
JavierA126600101

In this query, first we get the customer_name, order_id, order_value and quantity columns from the table. In ordering the rows, this query first gets rows where the quantity is greater than 120. (In this case, the quantity is 123.) Since we have no other rows that meet that criteria, the rest of the rows are ordered by order_id.

Using CASE in the WHERE Clause

The WHERE clause is used to filter records from the query results based on declared conditions. For example, if your company wants to waive shipping fees for orders over $100, you may first want to see how many orders will qualify and analyze the impact. The following WHERE clause will only count IDs for orders over $100:

SELECT  COUNT(order_id)
FROM    order_summary
WHERE   order_value > 100;

And the result:

COUNT(order_id)
4

On the basis of the result, you’ll assume around 4 orders will be impacted by this. Of course, this is the first step of your analysis; you’d likely want to do many more detailed analyses to quantify the impact.

Now let me take you through an example of using the WHERE clause with CASE. Take a look at the influencer_list table:

influencer_nameinfluencer_channelyoutube_channelfb_channelmonth_yeartotal_views
LouisyoutubehungryLouisJan_20213200
JanetfacebookstylenmakeupJan_2021423444
MichaelfacebookInfl_brandXJan_20212322
MichaelfacebookInfl_brandXFeb_2021240000

Let’s say that your business uses various influencers to promote your brands. You want to see all the influencers whose YouTube channel or Facebook account directly uses your name (‘BrandX’).

Each influencer has one type of channel/account. Here’s how you’d find out which ones mention BrandX:

SELECT DISTINCT influencer_name
FROM influencer_list
WHERE CASE WHEN influencer_channel = 'facebook' THEN fb_channel
	     WHEN influencer_channel = 'youtube' THEN youtube_channel
	     END LIKE '%brandX%';

Here’s the result:

influencer_name
Michael

The above query will return all rows where either youtube_channel or fb_channel has ‘brandX’ in it. How do we do this? Well, you know how WHERE and CASE WHEN work together. The new element here is LIKE '%brandX%'. All that does is tell the query to return the influencer channels that contain ‘BrandX’ in their name; LIKE is used to match the column value to the pattern, and the percent sign (%) indicates that any number of characters can come before or after ‘BrandX (which is why the % is at both ends of BrandX).

Using CASE in the HAVING Clause

The HAVING clause is used with the GROUP BY clause to filter the groups being displayed. For instance, if you wanted to see records from the influencer_list table where total_views over the influencer’s lifetime are greater than a million, you’d write:

SELECT      influencer_name,
	   SUM(total_views)
FROM     influencer_list
GROUP BY influencer_name
HAVING   SUM(total_views) > 200000;

And this is what you’d get:

influencer_nameSUM(total_views)
Michael242322
Janet423444

You can also use CASE with the HAVING clause. Say you want to get a list of influencers whose total views are greater than 100 for YouTube or greater than 400,000 for Facebook.

SELECT      influencer_name,
	   influencer_channel,
	   SUM(total_views)
FROM     influencer_list
GROUP BY influencer_name,
	   influencer_channel
HAVING   CASE WHEN influencer_channel = 'youtube' 
        THEN SUM(total_views) > 100
  WHEN influencer_channel = 'facebook' 
  THEN SUM(total_views) > 400000
   END;

And the result:

influencer_nameinfluencer_channelSUM(total_views)
Louisyoutube3200
Janetfacebook423444

This query first sums up the total views by influencer_name and influencer_channel. In the HAVING clause, we then filter only those groups which have more than 100 views for YouTube and more than 400,000 views for Facebook. Notice that Michael, who has 242,322 Facebook views does not feature in the output; his total is less than 400,000.

Using CASE in an UPDATE Statement

You can also use CASE in an UPDATE statement. The SQL UPDATE statement is used to change values in an existing table.

Imagine that you want to update the influencer_channel values in our current dataset by changing the channels to a two letter code: ‘youtube’ has to be changed to ‘yt’ and ‘facebook’ has to be changed to ‘fb’.

UPDATE influencer_list
SET     influencer_channel = CASE influencer_channel 
 			  WHEN 'youtube' THEN 'yt'
			  WHEN 'facebook' THEN 'fb'
			  ELSE 'invalid value'
			  END;

This is how the influencer_list table will look after the update:

influencer_nameinfluencer_channelyoutube_channelfb_channelmonth_yeartotal_views
LouisythungryLouisJan_20213200
JanetfbstylenmakeupJan_2021423444
MichaelfbInfl_brandXJan_20212322
MichaelfbInfl_brandXFeb_2021240000

You will notice that ‘youtube’ has been replaced with ‘yt’ and ‘facebook’ has been replaced with ‘fb’ in the influencer_channel column.

You can also use CASE to delete or insert rows in your tables. Read this article on  using CASE with data-modifying statements for more details.

Ready to Use CASE in SQL Queries?

After all these examples, I am sure that you have a better idea of how CASE works in SQL and the statement’s various potential applications. So, it is time you put your learning into action! Reading about SQL will surely help you learn, but if you want to become an expert, your mantra is “Practice!”.

I’d also recommend a good SQL practice course. The LearnSQL.com practice course uses practical examples and use cases, and you don’t need to set anything up to start – the Internet and a browser is enough.

The more queries you write, the better you will become at CASE and other SQL commands. Why wait? Get started now!