SQL CASE WHEN Explained with 8 Practical Examples

SQL CASE WHEN explained with examples

CASE WHEN is how you write IF logic in SQL. With it you can turn numbers into labels ("High", "Medium", "Low"), build conditional totals, sort in a custom order and clean messy values, all inside one query.

This guide covers the two forms of CASE, eight practical examples you will use at work, and the mistakes that give wrong results. The syntax works in MySQL, PostgreSQL, SQL Server, Oracle and SQLite.

Quick answer

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END
  • SQL checks the WHEN conditions from top to bottom and returns the result of the first one that is true.
  • If no condition is true and there is no ELSE, the result is NULL.
  • CASE can be used in SELECT, WHERE, ORDER BY, GROUP BY and inside aggregate functions such as SUM and COUNT.

Sample table

The examples use an orders table:

order_idcustomerregionstatusamount
1AshaNorthDelivered12000
2RahulSouthCancelled4500
3MeenaNorthDelivered56000
4KaranEastPending8000
5PriyaSouthDelivered23000
6VikasWestReturned15000

The two forms of CASE

Searched CASE (most common)

Each WHEN has its own full condition, so you can use >, BETWEEN, LIKE, IN, AND and OR.

CASE WHEN amount >= 50000 THEN 'High' ELSE 'Normal' END

Simple CASE

Compares one column with a list of exact values. It is shorter but can only test equality.

CASE region
    WHEN 'North' THEN 'N'
    WHEN 'South' THEN 'S'
    ELSE 'Other'
END

8 practical examples

1. Group values into bands

SELECT order_id,
       amount,
       CASE
           WHEN amount >= 50000 THEN 'High'
           WHEN amount >= 10000 THEN 'Medium'
           ELSE 'Low'
       END AS order_size
FROM orders;

Order matters. Order 3 (56,000) stops at "High". If you put amount >= 10000 first, every large order would be labelled "Medium".

2. Count by condition in one row

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

Result: 6 total, 3 delivered, 1 cancelled. This pattern is called conditional aggregation and is the most useful CASE trick for reports.

3. Conditional totals by group

SELECT region,
       SUM(CASE WHEN status = 'Delivered' THEN amount ELSE 0 END) AS delivered_sales,
       SUM(amount) AS all_sales
FROM orders
GROUP BY region;

North shows 68,000 delivered out of 68,000; South shows 23,000 delivered out of 27,500.

4. Pivot rows into columns

SELECT status,
       SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) AS north,
       SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) AS south,
       SUM(CASE WHEN region = 'East'  THEN amount ELSE 0 END) AS east,
       SUM(CASE WHEN region = 'West'  THEN amount ELSE 0 END) AS west
FROM orders
GROUP BY status;

This works in every database, unlike the PIVOT keyword, which only some databases support.

5. Percentage of a condition

SELECT
    ROUND(100.0 * SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) / COUNT(*), 1)
        AS delivery_rate_pct
FROM orders;

Result: 50.0. Multiply by 100.0, not 100, so the division is not done with whole numbers (in some databases 3/6 would give 0).

6. Custom sort order

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

Pending orders come first, which alphabetical sorting cannot do.

7. Clean and standardise values

SELECT customer,
       CASE
           WHEN UPPER(TRIM(region)) IN ('N', 'NORTH') THEN 'North'
           WHEN UPPER(TRIM(region)) IN ('S', 'SOUTH') THEN 'South'
           ELSE region
       END AS region_clean
FROM orders;

Useful when the same value is typed in different ways in the source system.

8. CASE in WHERE

SELECT *
FROM orders
WHERE amount >= CASE WHEN region = 'North' THEN 20000 ELSE 10000 END;

Each region gets a different minimum amount. Often a plain OR is easier to read, so use this only when the rule is truly different per group.

Using the CASE result in GROUP BY

To count orders per band, repeat the expression in GROUP BY or use a subquery:

SELECT order_size, COUNT(*) AS orders
FROM (
    SELECT CASE
               WHEN amount >= 50000 THEN 'High'
               WHEN amount >= 10000 THEN 'Medium'
               ELSE 'Low'
           END AS order_size
    FROM orders
) t
GROUP BY order_size;

Result: High 1, Medium 3, Low 2. Most databases do not let you use the alias order_size directly in GROUP BY of the same query (MySQL and PostgreSQL do).

Common mistakes

  • Wrong order of conditions: put the most specific or highest band first.
  • Forgetting ELSE: missing rows become NULL, and NULL is ignored by SUM and COUNT(column).
  • Mixed data types: THEN 'High' ELSE 0 mixes text and a number and fails in strict databases. Return the same type in every branch.
  • Testing NULL with =: WHEN discount = NULL is never true. Use WHEN discount IS NULL.
  • Missing END: every CASE needs its own END, including nested ones.

CASE vs IF vs IIF

MySQL has IF(condition, a, b) and SQL Server has IIF(condition, a, b). They are short, but they only exist in those databases. CASE is standard SQL and works everywhere, so it is the safer habit.

Frequently asked questions

Can I use multiple conditions in one WHEN?

Yes: WHEN region = 'North' AND amount > 10000 THEN ....

Can CASE be nested?

Yes, a THEN or ELSE can contain another CASE. Keep nesting shallow so the query stays readable.

Does CASE slow down a query?

Usually not noticeably. It is evaluated row by row like any other expression.

Practice task

Write one query that returns, for each region, the number of orders, the number of delivered orders and the delivery rate in percent. Then sort the result so the region with the lowest rate comes first.

Next, learn how to summarise data with SQL GROUP BY and HAVING, combine tables with SQL JOINs, and rank rows with SQL window functions.

Tags

Post a Comment

0 Comments
* Please Don't Spam Here. All the Comments are Reviewed by Admin.
Learn Excel, Power BI, and Finance in Simple Hindi and English Follow step-by-step tutorials and practical examples designed for students, job seekers, and working professionals. We cover Power BI dashboards, DAX formulas, Excel tricks, financial literacy, and AI tools.