CASE WHEN: classify orders without overlapping buckets

A CASE expression assigns a result according to conditions. In a searched CASE, the first true WHEN condition determines the output. That makes condition order important whenever ranges overlap.

For reporting buckets, a better starting point is to design nonoverlapping ranges before writing SQL. Specify what happens at each boundary, what NULL means and whether every eligible record receives a label.

We will classify the completed orders in the synthetic commerce lab. The amounts are integer paise, and the bands are arbitrary teaching choices—not recommended business thresholds.

Write the boundaries as a table

An amount of exactly 10,000 belongs to Medium, and exactly 20,000 belongs to Large. A zero-value order belongs to Small under this contract. Another business may treat zero separately, but that should be an explicit rule.

Writing the boundaries in words helps a stakeholder review the segmentation without reading SQL. It also gives you the boundary cases needed for a test.

Implement the ordered conditions

SELECT
    order_id,
    order_total_paise,
    CASE
        WHEN order_total_paise IS NULL THEN 'Unknown'
        WHEN order_total_paise < 0 THEN 'Invalid'
        WHEN order_total_paise < 10000 THEN 'Small'
        WHEN order_total_paise < 20000 THEN 'Medium'
        ELSE 'Large'
    END AS value_band
FROM orders
WHERE status = 'completed'
ORDER BY order_id;

The earlier conditions already exclude values handled above them. By the time the expression reaches the Medium condition, the value is known, nonnegative and at least 10,000.

The order matters. If you put amount < 20000 first, it captures the Small values as well. CASE will not continue looking for a more specific label after a true condition. SQLite CASE expression documentation.

Aggregate without duplicating the classification logic

WITH classified AS (
    SELECT order_id, order_total_paise,
        CASE
            WHEN order_total_paise IS NULL THEN 'Unknown'
            WHEN order_total_paise < 0 THEN 'Invalid'
            WHEN order_total_paise < 10000 THEN 'Small'
            WHEN order_total_paise < 20000 THEN 'Medium'
            ELSE 'Large'
        END AS value_band
    FROM orders
    WHERE status = 'completed'
)
SELECT value_band,
       COUNT(*) AS orders,
       SUM(order_total_paise) AS value_paise
FROM classified
GROUP BY value_band
ORDER BY value_band;

Expected results are Small: two orders, 17,000 paise; Medium: five orders, 62,000; Large: one order, 25,000. The counts sum to eight and the values sum to 104,000.

No Unknown or Invalid row appears because the fixture has no eligible order with those amounts. That does not mean those branches are unnecessary. They document how future inputs should be handled.

A chart may need to show zero-count categories consistently. In that case, use a separate band table and LEFT JOIN the aggregate to it rather than assuming every category will occur in the data.

Test the exact boundary values

Create a small independent fixture:

WITH examples(amount) AS (
    VALUES (NULL), (-1), (0), (9999), (10000), (19999), (20000)
)
SELECT amount,
    CASE
        WHEN amount IS NULL THEN 'Unknown'
        WHEN amount < 0 THEN 'Invalid'
        WHEN amount < 10000 THEN 'Small'
        WHEN amount < 20000 THEN 'Medium'
        ELSE 'Large'
    END AS value_band
FROM examples;

The intended labels, in the listed input sequence, are Unknown, Invalid, Small, Small, Medium, Medium and Large. If you need a guaranteed displayed order, add an explicit sequence column and ORDER BY it; VALUES input order is not a general output-order contract.

Boundary tests are more informative than checking only typical values. A mistaken <= often affects exactly the records that disappear between neighbouring ranges.

Distinguish exclusive buckets from multiple flags

One CASE expression returns one label. Sometimes the business needs several independent properties, such as “high value,” “refunded” and “new customer.” Those are not mutually exclusive categories, so separate boolean flags may be appropriate.

Do not force overlapping behaviours into a single hierarchy unless the priority order has business meaning. Otherwise, the first rule hides information that another team expects to see.

For complex or frequently changing bands, consider a controlled mapping table with effective dates and validation for overlapping ranges. A long CASE expression scattered across dashboards is difficult to maintain consistently.

Practice: move the Small/Medium boundary to 12,000 paise. Recalculate the counts and explain where the two 10,000-paise orders move. Then decide whether historical reports should be restated or retain the rule version used at publication.

The Data Analytics with Generative AI programme covers the SQL and reporting skills behind this example. Clear bucket definitions also make AI-generated summaries easier to verify because the model is given an explicit classification contract rather than vague labels.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in GROUP BY and HAVING for meaningful customer segments.
• Continue with SQL dates: choose inclusive and exclusive boundaries correctly.