CASE

MySQL's conditional expression in both forms, how it drives conditional aggregates, and how to pivot rows into columns with it.

SyntaxCASE WHEN condition THEN result [WHEN ...] [ELSE result] END

CASE is SQL's if/else, and it is an expression rather than a statement — it can appear anywhere a value can, including inside SELECT, ORDER BY, WHERE, and the argument list of an aggregate.

Two forms

The simple form, CASE value WHEN compare_value THEN result ... END, compares one expression against a list of candidates with =. The searched form, CASE WHEN condition THEN result ... END, evaluates independent boolean conditions and takes the first that is true. If nothing matches and there is no ELSE, the result is NULL. IF(expr1, expr2, expr3) is MySQL's shorthand for a two-branch searched CASE: IF(c, a, b) and CASE WHEN c THEN a ELSE b END return the same thing.

Inside aggregates

Putting CASE inside SUM() or COUNT() gives a conditional aggregate: SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) totals only the paid rows. With COUNT(), omit the ELSE so non-matching rows produce NULL, which COUNT() skips.

Pivoting

MySQL 8.4 has no PIVOT operator. One conditional aggregate per output column, grouped by the row key, is how rows become columns. The column list is fixed at write time, so a pivot over unknown categories has to be generated by the application.

Example

Example · sql
-- Monthly revenue by channel: rows pivoted into columns
SELECT
  DATE_FORMAT(o.placed_at, '%Y-%m')                                   AS month,
  -- One conditional aggregate per output column; ELSE 0 keeps sums numeric
  SUM(CASE WHEN o.channel = 'web'    THEN o.total ELSE 0 END)         AS web,
  SUM(CASE WHEN o.channel = 'store'  THEN o.total ELSE 0 END)         AS store,
  SUM(CASE WHEN o.channel = 'phone'  THEN o.total ELSE 0 END)         AS phone,
  -- No ELSE: non-matching rows are NULL, and COUNT skips NULLs
  COUNT(CASE WHEN o.total >= 500 THEN 1 END)                          AS big_orders,
  -- Searched form for a band; the first true branch wins
  CASE
    WHEN SUM(o.total) >= 100000 THEN 'tier 1'
    WHEN SUM(o.total) >=  25000 THEN 'tier 2'
    ELSE 'tier 3'
  END                                                                 AS month_tier
FROM orders o
WHERE o.status = 'paid'
  AND o.placed_at >= '2026-01-01'
GROUP BY DATE_FORMAT(o.placed_at, '%Y-%m')
ORDER BY month;

When to use it

  • Turn a status code into a human-readable label in the result set
  • Count matching and non-matching rows in a single pass over the table
  • Pivot category rows into one column per category for a summary report
  • Impose a business-defined sort order on a status or priority column

More examples

Simple form, searched form, and IF()

Every pair here produces the same value except the last, where the simple form silently reports 'set' for a NULL priority. Read this as the rule for choosing a form: simple for equality against constants, searched for anything involving NULL, ranges, or more than one column.

Example · sql
SELECT
  -- Simple: compares priority against each candidate with =
  CASE priority WHEN 1 THEN 'high' WHEN 2 THEN 'normal' ELSE 'low' END   AS a,
  -- Searched: the same result written as independent conditions
  CASE WHEN priority = 1 THEN 'high'
       WHEN priority = 2 THEN 'normal'
       ELSE 'low' END                                                    AS b,
  -- Two branches only: IF() is the shorthand
  IF(priority = 1, 'high', 'other')                                      AS c,
  CASE WHEN priority = 1 THEN 'high' ELSE 'other' END                    AS c_long,
  -- The simple form cannot test NULL; the searched form can
  CASE priority WHEN NULL THEN 'missing' ELSE 'set' END                  AS never_missing,
  CASE WHEN priority IS NULL THEN 'missing' ELSE 'set' END               AS correct
FROM tickets;

Custom ordering without a lookup table

Alphabetical order on a status column is rarely the order a human wants. A CASE in ORDER BY imposes a business sequence without adding a sort_order column. It costs a filesort, so for large result sets add the numeric column to the table and index it instead.

Example · sql
SELECT id, status, created_at
FROM tickets
ORDER BY
  CASE status
    WHEN 'open'        THEN 1
    WHEN 'in_progress' THEN 2
    WHEN 'waiting'     THEN 3
    WHEN 'closed'      THEN 4
    ELSE 5
  END,
  created_at DESC;

Counting and rating in one pass

Conditional aggregates replace what would otherwise be three correlated subqueries or three passes over the table. Note the deliberate difference: COUNT wants no ELSE so misses become NULL, while SUM wants ELSE 0 so misses contribute nothing.

Example · sql
SELECT
  agent_id,
  COUNT(*)                                                    AS handled,
  -- ELSE omitted, so only matching rows are counted
  COUNT(CASE WHEN resolved_at IS NOT NULL THEN 1 END)         AS resolved,
  -- ELSE 0 because this one is summed, not counted
  SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END)                AS good_ratings,
  ROUND(100 * SUM(CASE WHEN rating >= 4 THEN 1 ELSE 0 END)
            / NULLIF(COUNT(rating), 0), 1)                    AS good_pct
FROM tickets
WHERE created_at >= CURDATE() - INTERVAL 30 DAY
GROUP BY agent_id;

Related

sql pivotsql coalescesql countsql group bysql filter clause

Discussion

  • Be the first to comment on this lesson.