CASE: Conditional Columns

CASE is SQL's if/else

CASE is SQL's if/else. It lets you compute a different value per row based on conditions: bucketing a numeric measure into labels, mapping codes to names, or (the DE power move) pivoting rows into columns via conditional aggregation.

Searched CASE: the general form

Table

Each total_cents falls through the WHEN branches top-to-bottom; the first true branch sets size_bucket. 9900 is not >= 10000 so it lands in 'medium', while 10000 crosses the threshold into 'large'.

order_id	total_cents	size_bucket
100	500	small
101	2000	medium
102	9900	medium
103	10000	large
104	15000	large

Conditions can be anything:

SELECT
  order_id,
  CASE
    WHEN total_cents >= 10000 THEN 'large'
    WHEN total_cents >= 2000  THEN 'medium'
    ELSE 'small'
  END AS size_bucket
FROM orders;

Conditions are tested top-to-bottom; the first true branch wins, so order them from most to least specific. If none match and there's no ELSE, the result is NULL.

Simple CASE: shorthand for equality checks

Shorthand when you're comparing one expression to constants:

CASE status WHEN 'paid' THEN 1 WHEN 'cancelled' THEN 0 ELSE NULL END

Anatomy

CASE WHEN cond1 THEN val1     ← first matching branch wins
     WHEN cond2 THEN val2
     ELSE fallback            ← optional; without it, no-match → NULL
END AS alias

The conditional-aggregation trick: pivoting rows into columns

Wrap a CASE inside an aggregate and you turn categories into columns. To count paid vs cancelled side by side, per day:

SELECT
  order_date,
  SUM(CASE WHEN status = 'paid'      THEN 1 ELSE 0 END) AS paid_count,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count
FROM orders
GROUP BY order_date;

Each CASE emits 1 for the rows it wants and 0 otherwise; the SUM counts them. This is how you build a classic reporting mart (one row per day, one column per status) without a dedicated PIVOT operator.

In the warehouse this differs: PIVOT. Snowflake, SQL Server, and others ship a dedicated PIVOT operator, but its syntax is warehouse-specific. Conditional aggregation with SUM(CASE …) is the fully portable equivalent (it runs on every engine, including SQLite), so we author pivots that way here.

Keep it readable / common pitfall

Every branch of a CASE should return the same type. Mixing 'small' (text) and 0 (number) across branches yields inconsistent typing that some engines coerce and others reject. Also: COUNT(CASE WHEN … THEN 1 END) works too (COUNT skips the NULL from the missing ELSE), but SUM(CASE … THEN 1 ELSE 0 END) is the clearer, more portable idiom.

Recap

CASE branches a value per row (first true WHEN wins) for bucketing; wrapped inside SUM/COUNT it becomes conditional aggregation: the portable way to pivot categories into side-by-side columns.