SQL CASE Statements for Data Analysts

Raw data rarely arrives in the categories a business thinks in. The database stores an order amount of 8500; the report needs to call it a "High value" order. It stores a first-order flag; the dashboard needs "New" versus "Returning". The CASE statement is how an analyst applies this if-then logic inside a query, converting raw values into the labels, tiers and segments that make a report readable. It is one of the most quietly useful tools in analytics SQL, and it powers a clever trick called conditional aggregation.

This tutorial pairs with aggregate functions and GROUP BY, since CASE combined with those is where it shines. It sits comfortably in the mid-to-late SQL for analytics path.

Basic CASE: turning values into labels

The searched form evaluates conditions in order and returns the first match:

-- orders
order_id | customer | amount
---------+----------+-------
1001     | Aarti    | 1200
1002     | Bhaskar  | 8500
1003     | Chitra   | 600
1004     | Devan    | 4000
1005     | Esha     | 9000

To bucket each order into a value tier:

SELECT customer, amount,
       CASE
           WHEN amount < 1000 THEN 'Low'
           WHEN amount < 5000 THEN 'Medium'
           ELSE 'High'
       END AS value_tier
FROM orders;

customer | amount | value_tier
---------+--------+-----------
Aarti    | 1200   | Medium
Bhaskar  | 8500   | High
Chitra   | 600    | Low
Devan    | 4000   | Medium
Esha     | 9000   | High

CASE checks each WHEN top to bottom and stops at the first true one, which is why the order of conditions matters: because the "Low" test runs first, the "Medium" test only needs an upper bound. The ELSE catches everything left over. Leave out ELSE and unmatched rows return NULL — usually a bug, so include it deliberately.

Simple versus searched CASE

There are two forms. The simple form compares one expression to fixed values:

SELECT customer,
       CASE region
           WHEN 'South' THEN 'Domestic'
           WHEN 'West'  THEN 'Domestic'
           ELSE 'Other'
       END AS market
FROM orders;

The searched form (the first example) uses full boolean conditions and handles ranges and multiple columns. Analysts reach for the searched form most, because real classification rules involve thresholds and combinations, not just equality.

Conditional aggregation: the pivot trick

The most powerful use of CASE is inside an aggregate. Because SUM ignores nothing and CASE can return 0 for non-matching rows, SUM(CASE WHEN ... THEN amount ELSE 0 END) totals only the rows you want. This lets you produce several conditional measures in one row — effectively pivoting rows into columns:

SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN amount >= 5000 THEN 1 ELSE 0 END) AS high_value_orders,
    SUM(CASE WHEN amount <  5000 THEN 1 ELSE 0 END) AS other_orders,
    SUM(CASE WHEN amount >= 5000 THEN amount ELSE 0 END) AS high_value_revenue
FROM orders;

total_orders | high_value_orders | other_orders | high_value_revenue
-------------+-------------------+--------------+-------------------
5            | 2                 | 3            | 17500

In one query you get counts and revenue split by tier, side by side. Add a GROUP BY region and you can build a full cross-tab — one column per category — without a dedicated pivot feature. This conditional-aggregation pattern is a signature analyst technique and appears in nearly every summary dashboard.

CASE in ORDER BY

CASE can also drive custom sort orders that are not alphabetical. To sort tiers as High, Medium, Low rather than alphabetically:

SELECT customer, amount
FROM orders
ORDER BY CASE
    WHEN amount >= 5000 THEN 1
    WHEN amount >= 1000 THEN 2
    ELSE 3
END;

This maps each tier to a sort key, producing a business-meaningful order. It is how you make "Critical, High, Medium, Low" sort correctly instead of alphabetically.

Practical usage

Analysts use CASE constantly: segmenting customers into value tiers, labeling new versus returning buyers, grouping products into categories, flagging outliers, and pivoting data for dashboards. It keeps classification logic inside SQL rather than pushing it into a spreadsheet afterward, which means the definition of "High value" lives in one auditable place. Combined with GROUP BY, conditional aggregation replaces many manual pivot tables entirely.

Common mistakes

• Missing ELSE. Without ELSE, unmatched rows become NULL, quietly polluting reports. Always add an explicit fallback like ELSE 'Other'.
• Overlapping or misordered conditions. Because CASE stops at the first true WHEN, wrong ordering mislabels rows. Order boundaries from one extreme to the other and let each condition assume the previous ones failed.
• Counting with SUM of a boolean the wrong way. SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts matches; using COUNT(CASE WHEN ... THEN 1 END) also works because COUNT ignores NULL, but mixing the two up leads to wrong totals. Pick one pattern and be consistent.
• Putting the whole expression in GROUP BY incorrectly. When grouping by a CASE-derived label, repeat the full CASE expression in GROUP BY (or group by the alias where the database allows it), or the query errors.

In interviews

CASE questions test whether you can classify and pivot: "bucket customers into spend tiers and count each", "show revenue by region as separate columns in one row", or "count new versus returning customers". The pivot-with-conditional-aggregation question is a favourite because it reveals whether a candidate knows the SUM(CASE ...) idiom. Interviewers also check that you handle the no-match case with ELSE and order your conditions correctly.

Where this fits in your learning path

CASE turns the raw values you filter with SELECT and WHERE into business categories, and combined with aggregate functions and GROUP BY it powers conditional aggregation and pivots. It is a compact but high-leverage skill on the data analyst roadmap, used in almost every report you will build.