Home › Blog › SQL CASE WHEN Tutorial

SQL CASE WHEN Tutorial — Conditional Logic, Pivots, and the Common Bugs

CASE WHEN is the SQL equivalent of an if/else expression — and the most flexible piece of conditional logic the language gives you. It works in SELECT, WHERE, ORDER BY, GROUP BY, and inside aggregate functions. The simple syntax hides three power moves most analysts don't reach for: conditional aggregation, custom sort orders, and CASE-based pivoting. This guide walks every place CASE belongs and the bugs that catch most people.

Two syntaxes — simple and searched

CASE has two forms. They look similar but compile to different things.

Simple CASE — match an expression against literals

SELECT name,
       CASE status
         WHEN 'active'  THEN 1
         WHEN 'pending' THEN 2
         WHEN 'paused'  THEN 3
         ELSE 0
       END AS status_code
FROM users;

Reads top-down, returns the first match. Compact when you're mapping one expression to many literals. Each WHEN can only test equality against the expression at the top.

Searched CASE — full boolean conditions

SELECT name,
       CASE
         WHEN amount > 10000            THEN 'whale'
         WHEN amount > 1000             THEN 'large'
         WHEN amount > 100              THEN 'medium'
         WHEN amount IS NULL             THEN 'unknown'
         ELSE 'small'
       END AS tier
FROM orders;

Each WHEN gets a full boolean expression — different columns, different operators, IS NULL, compound predicates with AND/OR. Searched CASE handles ~95% of real cases because most conditional logic is more complex than "match one value against literals."

CASE in SELECT — the everyday usage

The most common CASE pattern: bucketing a continuous column into discrete labels for reporting.

SELECT customer_id, total_spend,
       CASE
         WHEN total_spend >= 10000 THEN 'platinum'
         WHEN total_spend >= 2500  THEN 'gold'
         WHEN total_spend >= 500   THEN 'silver'
         ELSE 'bronze'
       END AS tier
FROM customers;

Always include an ELSE clause unless you genuinely want NULL for unmatched cases. The implicit ELSE is NULL, which silently propagates downstream and creates "wrong number" bugs.

CASE in ORDER BY — custom sort orders

Sometimes the natural sort order — alphabetical, numeric — isn't what the report needs. CASE in ORDER BY lets you encode a custom hierarchy.

SELECT ticket_id, status, opened_at
FROM support_tickets
ORDER BY
  CASE status
    WHEN 'urgent'  THEN 1
    WHEN 'open'    THEN 2
    WHEN 'pending' THEN 3
    WHEN 'closed'  THEN 4
    ELSE 5
  END,
  opened_at DESC;

Without the CASE, ORDER BY status would sort alphabetically: closed, open, pending, urgent. The CASE assigns priority numbers so urgent comes first regardless of alphabet. Common in dashboards and operational reports.

CASE in WHERE — when it earns its place

CASE in WHERE is unusual but legal. Most of what people write as WHERE CASE WHEN ... is cleaner as a compound predicate with AND/OR. The exception: when you need different filter logic per row, parameterized by another column.

SELECT *
FROM orders
WHERE
  CASE tier
    WHEN 'platinum' THEN amount > 100
    WHEN 'gold'     THEN amount > 50
    ELSE amount > 10
  END;

Same filter expression in OR form would be: (tier = 'platinum' AND amount > 100) OR (tier = 'gold' AND amount > 50) OR (tier NOT IN ('platinum','gold') AND amount > 10). The CASE form is sometimes more readable when you have many tier branches.

CASE in GROUP BY — segmentation

GROUP BY accepts expressions, including CASE. This is how you group by a derived bucket without first putting it in a CTE or subquery.

SELECT
  CASE
    WHEN total_spend >= 10000 THEN 'platinum'
    WHEN total_spend >= 2500  THEN 'gold'
    WHEN total_spend >= 500   THEN 'silver'
    ELSE 'bronze'
  END AS tier,
  COUNT(*) AS n
FROM customers
GROUP BY
  CASE
    WHEN total_spend >= 10000 THEN 'platinum'
    WHEN total_spend >= 2500  THEN 'gold'
    WHEN total_spend >= 500   THEN 'silver'
    ELSE 'bronze'
  END;

Most databases require the GROUP BY to repeat the expression rather than reference the SELECT alias. Postgres and BigQuery accept aliases; SQL Server and Oracle don't. To avoid the duplication, wrap the CASE in a CTE:

WITH tiered AS (
  SELECT customer_id, total_spend,
         CASE
           WHEN total_spend >= 10000 THEN 'platinum'
           WHEN total_spend >= 2500  THEN 'gold'
           WHEN total_spend >= 500   THEN 'silver'
           ELSE 'bronze'
         END AS tier
  FROM customers
)
SELECT tier, COUNT(*) AS n
FROM tiered
GROUP BY tier;

CASE in aggregates — the workhorse

This is the single most useful CASE pattern in business reporting: putting a CASE expression inside an aggregate function. It lets you compute multiple per-condition metrics in a single GROUP BY query.

SELECT department,
       COUNT(*) AS total_orders,
       SUM(CASE WHEN status = 'shipped'  THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'pending'  THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN status = 'cancelled'THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status = 'paid'     THEN amount END) AS revenue,
       AVG(CASE WHEN status = 'pending'  THEN priority END) AS avg_pending_pri
FROM orders
GROUP BY department;

Three things make this pattern shine:

• The ELSE branch matters. SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts matches because the false branch contributes 0. Without ELSE, the false branch is NULL and SUM still skips NULL — so SUM(CASE WHEN ... THEN 1 END) also works for counts. Use whichever reads clearer to you; both are correct.
• Skip ELSE for AVG and SUM-of-amounts. AVG(CASE WHEN active THEN response_time END) averages only the active rows because NULL is skipped. If you'd written ELSE 0, the zeros would pull the average down, producing the wrong number.
• One scan, many metrics. The query reads each row exactly once and folds it into all the conditional aggregates simultaneously. The alternative — multiple queries with different WHERE clauses, then joined — reads the table N times.

Pivoting rows into columns with CASE

"Pivot" means turning row-shaped data into column-shaped data. SQL Server, Oracle, and Snowflake have explicit PIVOT operators. Everywhere else, conditional aggregation IS the pivot.

department | status     | n
-----------+------------+----
Eng        | shipped    | 12
Eng        | pending    |  3
Sales      | shipped    |  8
Sales      | pending    |  5

department | shipped | pending
-----------+---------+--------
Eng        |     12  |      3
Sales      |      8  |      5

SELECT department,
       SUM(CASE WHEN status = 'shipped' THEN n ELSE 0 END) AS shipped,
       SUM(CASE WHEN status = 'pending' THEN n ELSE 0 END) AS pending
FROM status_counts
GROUP BY department;

One CASE-wrapped SUM per column you want in the output. Works on every database. The downside: if you have 50 statuses, you write 50 CASE branches. That's where dynamic SQL or PIVOT operators help — but for the 80% case (3-10 columns), CASE is plenty.

CASE and NULL — the trap

The biggest CASE bug comes from how SQL handles NULL in equality comparisons.

SELECT
  CASE status
    WHEN 'active' THEN 1
    WHEN NULL     THEN 0     -- ⚠ never matches, even when status IS NULL
    ELSE -1
  END
FROM users;

Reason: status = NULL evaluates to unknown, never true, so the simple-CASE comparison never matches. To handle NULL inside a CASE you must use the searched form:

SELECT
  CASE
    WHEN status IS NULL      THEN 0
    WHEN status = 'active' THEN 1
    ELSE -1
  END
FROM users;

Always handle NULL first in a searched CASE — before any equality WHEN. Otherwise NULL rows fall through to the ELSE branch and pick up the wrong label.

The 4 common bugs

Bug 1: Forgot ELSE, got NULL by surprise

An unmatched CASE returns NULL silently. Downstream code that expects a string or number then breaks in non-obvious ways. Always include ELSE unless NULL is genuinely intentional.

Bug 2: Conditions in the wrong order

CASE evaluates top-down and returns the first match. WHEN amount > 100 THEN 'medium' WHEN amount > 1000 THEN 'large' always returns 'medium' for everything over 100, including 1500. Order from most specific to most general.

Bug 3: WHEN NULL doesn't catch NULLs (the simple-CASE NULL trap)

Already covered above. Use searched CASE with IS NULL.

Bug 4: Type mismatch across branches

SELECT
  CASE
    WHEN tier = 'gold' THEN 1            -- integer
    WHEN tier = 'silver' THEN 'unknown'  -- string
    ELSE 0.0
  END
FROM users;

Most databases either error or implicitly cast everything to the most-general type, which is usually not what you want. Pick a single output type per CASE expression and use explicit CAST if you need conversion.

CASE vs COALESCE vs IIF vs DECODE

If portability matters, default to CASE WHEN. If readability matters more and you have exactly two branches in SQL Server or BigQuery, IIF is fine. COALESCE for NULL-default chains. DECODE is Oracle-only legacy — translate to CASE when you can.

Practice with real challenges

Reading a CASE example and writing one cold are different skills. Drilling 8+ CASE-tagged challenges is the fastest way to internalize the patterns — especially conditional aggregation, which is the single highest-leverage SQL pattern most analysts under-use.

Found a typo or want a topic covered? Email us.