SQL CASE WHEN

CASE WHEN adds if-then-else logic to a query. It checks conditions in order and returns the value for the first one that's true. Analysts use it to bucket numbers into bands, turn codes into labels, and count things conditionally.

Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

There's also a shorter "simple" form for comparing one column to fixed values:

CASE column
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

Examples

1. Price bands

SELECT product_name,
       price,
       CASE
         WHEN price >= 10000 THEN 'Premium'
         WHEN price >= 2000  THEN 'Mid-range'
         ELSE 'Budget'
       END AS price_band
FROM products
ORDER BY price DESC
LIMIT 8;

Result:

(8 rows)

Because CASE stops at the first true condition, a ₹23,999 phone is labelled Premium and never reaches the Mid-range check.

2. Simple CASE to relabel codes

SELECT order_id,
       payment_method,
       CASE payment_method
         WHEN 'COD' THEN 'Cash on Delivery'
         WHEN 'UPI' THEN 'UPI'
         ELSE 'Online (Card/NetBanking)'
       END AS payment_type
FROM orders
LIMIT 5;

Result:

(5 rows)

3. Delivery speed

SELECT order_id,
       delivery_date - order_date AS days,
       CASE
         WHEN delivery_date IS NULL THEN 'Not delivered'
         WHEN delivery_date - order_date <= 3 THEN 'Fast'
         ELSE 'Slow'
       END AS delivery_speed
FROM orders
WHERE order_id BETWEEN 1001 AND 1006;

Result:

(6 rows)

Check for NULL first; otherwise NULL days would fall through to ELSE 'Slow'.

4. Conditional counts (CASE inside an aggregate)

This is the most powerful use of CASE. Count orders by outcome in a single row:

SELECT COUNT(*) AS total_orders,
       SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status IN ('Cancelled', 'Returned') THEN 1 ELSE 0 END) AS lost
FROM orders;

Result:

(1 row)

This pattern is the basis of pivoting data in SQL.

5. Custom sort order

Sort orders by how urgently they need attention, not alphabetically:

SELECT order_id, status
FROM orders
WHERE status <> 'Delivered'
ORDER BY CASE status
           WHEN 'Pending' THEN 1
           WHEN 'Shipped' THEN 2
           WHEN 'Returned' THEN 3
           ELSE 4
         END,
         order_id;

Result:

(8 rows)

Common mistakes

• Forgetting END. Every CASE must close with END.
• Wrong condition order. WHEN price >= 2000 before WHEN price >= 10000 labels everything above ₹2,000 as Mid-range.
• Mixed result types. All THEN values should be the same type. Don't return a number in one branch and text in another.
• No ELSE. Unmatched rows become NULL, which may be what you want, but it should be deliberate.

Practice question

Label each customer as "Early adopter" if they signed up before 2025, otherwise "New". Show name, signup date and the label.

SELECT customer_name,
       signup_date,
       CASE
         WHEN signup_date < '2025-01-01' THEN 'Early adopter'
         ELSE 'New'
       END AS customer_type
FROM customers
ORDER BY signup_date
LIMIT 8;

Key takeaways

• CASE WHEN ... THEN ... ELSE ... END is SQL's if-then-else.
• Conditions are checked in order; the first true one wins.
• Without ELSE, unmatched rows return NULL.
• SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts rows that meet a condition.