The world’s leading publication for data science, AI, and ML professionals.

3 Use Cases for SQL Case When Statement

Explained with examples

Photo by Tobias Fischer on Unsplash
Photo by Tobias Fischer on Unsplash

Simply put, the CASE WHEN statement evaluates given conditions and returns results based on this evaluation.

We can use it for deriving new columns from the existing ones. But, there are other cases where CASE WHEN comes in handy to solve complex tasks.

What we’ll learn in this article:

  • Use CASE WHEN to derive new columns
  • Use CASE WHEN in GROUP BY
  • Use CASE WHEN in calculating aggregations based on derived categories

We’ll be querying a product_inventory table I created with mock data. Here are the first five rows of this table:

(image by author)
(image by author)

1. Use Case When to Derive New Columns

The CASE WHEN (or CASE) statement allows for evaluating conditions whose results can be used to create new columns.

(image by author)
(image by author)

For instance, we can create a new column price_group with values low, medium, high based on product prices. It can be considered as creating product price groups.

SELECT
 product_description,
 price,
 CASE
  WHEN price > 20 THEN 'high'
  WHEN price <= 20 AND price > 10 THEN 'medium'
  WHEN price <= 10 THEN 'low'
 END AS price_group
FROM product_inventory
The first 10 rows of the output of the query above (image by author)
The first 10 rows of the output of the query above (image by author)

The CASE WHEN statement creates the product_column based on the following criteria:

  • If the price is higher than 20, the value is "high".
  • If the price is between 10 and 20, the value is "medium".
  • If the price is less than 10, the value is "low".

We can also write this query as follows:

SELECT
 product_description,
 price,
 CASE
  WHEN price > 20 THEN 'high'
  WHEN price <= 20 AND price > 10 THEN 'medium'
  ELSE 'low'
 END AS price_group
FROM product_inventory

In this case, the last condition is replaced with ElSE , which covers all the rows that do not meet the other specified conditions.


2. Use Case When in Group By

The CASE WHEN statement creates a column so it can also be used in the GROUP BY statement.

Consider the previous example where we create price categories for products. Let’s say we want to calculate the average stock quantity for each price group.

This can be done by simply putting the CASE WHEN statement in the GROUP BY :

SELECT
 CASE
  WHEN price > 20 THEN 'high'
  WHEN price <= 20 AND price > 10 THEN 'medium'
  ELSE 'low'
 END AS price_group,
 AVG(stock_qty) AS avg_stock
FROM product_inventory
GROUP BY
    CASE
  WHEN price > 20 THEN 'high'
  WHEN price <= 20 AND price > 10 THEN 'medium'
  ELSE 'low'
 END

The output of this query:

(image by author)
(image by author)

You may be asking why we can’t just write GROUP BY product_inventory . The reason is the order of execution of SQL queries. The SELECT statement and aliases are executed last so when SQL is executing the GROUP BY statement, it has no idea what product_category is.


3. Use Case When in Calculating Aggregations Based on Derived Categories

We can also use the CASE WHEN statement in aggregate functions such as SUM , AVG , MAX , and so on.

We want to group the products into price categories and find the number of products in each category. To do this, we can use CASE WHEN statements in SUM function:

SELECT
 SUM(
  CASE
   WHEN price > 20 THEN 1
   ELSE 0
  END
 ) AS number_of_high_price_products,
 SUM(
  CASE
   WHEN price <= 20 AND price > 10 THEN 1
   ELSE 0
  END
 ) AS number_of_medium_price_products,
 SUM(
  CASE
   WHEN price <= 10 THEN 1
   ELSE 0
  END
 ) AS number_of_low_price_products
FROM product_inventory

The output of this query:

(image by author)
(image by author)

In each SUM function, we create a column that takes the value of 1 in the rows that fit the condition and 0 in other rows. Thus, taking the sum of this column gives us the number of products that fit the given condition.


CASE WHEN is highly useful especially when doing analysis with SQL. The examples we did can also be done with other Data Analysis tools such Pandas. However, being able to do them while retrieving the data from the database is much more efficient and has the potential to save you both time and memory.

You can become a Medium member to unlock full access to my writing, plus the rest of Medium. If you already are, don’t forget to subscribe if you’d like to get an email whenever I publish a new article.

Thank you for reading. Please let me know if you have any feedback.


Related Articles