SQL CASE WHEN: Create Labels and Conditional Totals

A database stores numbers, but a report often needs categories such as large order or standard order. SQL CASE WHEN lets you define those labels inside a query. You can then combine the same idea with aggregation to count records that meet a condition.

Practice setup: These lessons use SQLite and the fictional shop database from the QueryFun SQL practice lab. Open that lab, run its setup once in a fresh practice database, then return here. Do not run the INSERT statements again for each lesson. You can use SQLite Fiddle without installing a database server.

The customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional currency units. Each order belongs to one customer. A customer can have several orders.

Turn an amount into a readable label

Imagine a fictional shop calls an order large when its amount is at least 1000. Write the rule explicitly:

SELECT order_id, amount,
       CASE
         WHEN amount >= 1000 THEN 'Large'
         ELSE 'Standard'
       END AS order_band
FROM orders
ORDER BY order_id;

Orders 101 and 104 are Large. Orders 102 and 103 are Standard. CASE is an expression: it produces one value for each row here. END closes the expression, and AS names the resulting column. This does not add a saved category to orders.

The threshold is an example business rule, not a universal definition of a large order. Before using a label in a real report, agree on its meaning, boundary values and units with the people who use the report.

Put more specific conditions first

For three bands, test the highest threshold before the lower threshold:

SELECT order_id, amount,
       CASE
         WHEN amount >= 1500 THEN 'High'
         WHEN amount >= 1000 THEN 'Medium'
         ELSE 'Low'
       END AS order_band
FROM orders
ORDER BY order_id;

The labels are Medium, Low, Low and High for order IDs 101 through 104. CASE takes the result from the first true WHEN. If amount >= 1000 came first, order 104 would be Medium and never reach the 1500 test.

You do not need to repeat amount < 1500 in the second condition because the first condition already handled amounts of 1500 or more. For beginners, write the rules out in words first, then compare their order with the SQL.

Make the fallback deliberate

If no WHEN matches and you omit ELSE, CASE returns NULL. An explicit ELSE usually makes a reporting rule easier to understand. When the input can be missing, consider a first condition such as WHEN amount IS NULL THEN 'Unknown' rather than automatically calling the missing amount Low.

Our lab amount column does not allow NULL, so missing amounts are not part of this sample. Real data may also contain cancellations, refunds or negative amounts. Decide whether these need their own bands instead of allowing a convenient default to hide them.

Count only matching rows with a conditional sum

Assign 1 to qualifying orders and 0 to the others, then add those values:

SELECT customer_id,
       COUNT(*) AS all_orders,
       SUM(CASE WHEN amount >= 1000 THEN 1 ELSE 0 END)
         AS large_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Customer 1 has 2 total orders and 1 large order. Customer 2 has 1 and 0. Customer 3 has 1 and 1. This technique is called conditional aggregation because the value being aggregated depends on a condition.

Be careful with COUNT(CASE WHEN ... THEN 1 ELSE 0 END). Both 1 and 0 are non-NULL, so COUNT counts both. SUM adds the flags and gives the intended matching-row count. An alternative COUNT pattern omits ELSE so nonmatches become NULL, but the SUM version is often easier to explain.

Try it yourself

Calculate two totals in a single output row: the combined amount of orders worth at least 1000, and the combined amount of orders below 1000. Check that the two results add back to the full order total.

SELECT SUM(CASE WHEN amount >= 1000 THEN amount ELSE 0 END)
         AS large_order_total,
       SUM(CASE WHEN amount < 1000 THEN amount ELSE 0 END)
         AS smaller_order_total
FROM orders;

Keep learning: Return to the SQL learning path to choose your next lesson.

Further reading: SQLite SELECT documentation.