How to Use CASE WHEN in BigQuery
Last updated August 30, 2026 · By the SaturnSQL team
CASE WHEN condition THEN value ... ELSE fallback END works anywhere an expression does in BigQuery - SELECT, WHERE, GROUP BY, ORDER BY. Conditions are checked top to bottom and the first match wins; without ELSE the result is NULL.
SELECT
order_id,
CASE
WHEN order_total >= 500 THEN 'large'
WHEN order_total >= 100 THEN 'medium'
ELSE 'small'
END AS order_size
FROM orders;The simple CASE form
When every branch compares the same expression against a value, name it once after CASE. Note that this form compares with =, so it never matches NULL; use the searched form with IS NULL for that.
SELECT
CASE plan
WHEN 'starter' THEN 19
WHEN 'pro' THEN 29
ELSE 0
END AS monthly_price
FROM subscriptions;Conditional aggregation
CASE inside an aggregate is the classic way to count or sum a subset per group. BigQuery also has COUNTIF, which reads better when all you need is a conditional count.
SELECT
signup_month,
SUM(CASE WHEN plan = 'pro' THEN 1 ELSE 0 END) AS pro_signups,
COUNTIF(plan = 'free') AS free_signups
FROM accounts
GROUP BY signup_month;Grouping by a CASE expression
SELECT
CASE
WHEN age < 25 THEN '18-24'
WHEN age < 45 THEN '25-44'
ELSE '45+'
END AS age_band,
COUNT(*) AS users
FROM users
GROUP BY age_band;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free