CASE WHEN

CASE WHEN works like a conditional label-maker built into your query - it checks each row against a list of rules you define and stamps on whichever value matches first.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Creating Retail Labels with CASE WHEN

SELECT order_id, revenue,
  CASE
    WHEN revenue < 50 THEN 'Low'
    WHEN revenue < 200 THEN 'Mid'
    ELSE 'High'
  END AS order_tier
FROM orders

Result:

order_id	revenue	order_tier
O2001	15.5	Low
O2002	220.0	High
O2003	85.75	Mid
O2004	9.99	Low
O2005	540.0	High

Step by step

One WHEN/THEN pair handles a single condition; rows that don't match get NULL (or the ELSE if present).
```
SELECT
  CASE
    WHEN revenue < 50 THEN 'Low'
    ELSE 'High'
  END AS category
FROM orders;;
```

Add a second WHEN - SQL checks the conditions top-down and uses the first match.

SELECT
  CASE
    WHEN revenue < 50 THEN 'Low'
    WHEN revenue < 200 THEN 'Mid'
    ELSE 'High'
  END AS category
FROM orders;;

ELSE catches every row that didn't match any WHEN - gives you a default label.

SELECT
  CASE
    WHEN revenue < 50 THEN 'Low'
    WHEN revenue < 200 THEN 'Mid'
    ELSE 'High'
  END AS category
FROM orders;;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query that labels each row 'high' when revenue is greater than 89.0, otherwise 'low'.

SELECT CASE WHEN revenue > 89.0 THEN 'high' ELSE 'low' END FROM orders

2. Using products, write a query that labels each row 'high' when price is greater than 22.5, otherwise 'low'.

SELECT CASE WHEN price > 22.5 THEN 'high' ELSE 'low' END FROM products

3. Using orders, label each order 'high' when revenue is greater than 150, otherwise 'low'.

SELECT order_id, CASE WHEN revenue > 150 THEN 'high' ELSE 'low' END AS size FROM orders

4. Using store_sales, write a query that labels each row 'high' when revenue is greater than 85.0, otherwise 'low'.

SELECT CASE WHEN revenue > 85.0 THEN 'high' ELSE 'low' END FROM store_sales

5. Using returns, write a query that labels each row 'high' when refund_amount is greater than 74.75, otherwise 'low'.

SELECT CASE WHEN refund_amount > 74.75 THEN 'high' ELSE 'low' END FROM returns

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed