
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
inGROUP 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:

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.

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 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:

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:

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.