SQL CASE WHEN Statement: Syntax, Patterns, and Examples

Conditional aggregation shows up in 18% of pivot prompts in our verified corpus. You wrap CASE inside COUNT or SUM to filter rows inline, swapping four scans for one. An analytics team at a rideshare company cut a daily job from 47 minutes to 90 seconds by collapsing five filtered CTEs into one conditional aggregation block. It's the move interviewers watch for.

SELECT
  department,
  COUNT(*) AS total_employees,
  COUNT(CASE WHEN salary > 100000 THEN 1 END) AS high_earners,
  SUM(CASE WHEN status = 'active' THEN salary ELSE 0 END) AS active_payroll,
  ROUND(
    100.0 * COUNT(CASE WHEN tenure_years >= 5 THEN 1 END) / COUNT(*),
    1
  ) AS pct_senior
FROM employees
GROUP BY department;

Before PIVOT syntax existed (and on engines that still lack it), CASE WHEN inside aggregation was the standard way to pivot. You create one CASE expression per output column, each filtering for a different value. This technique works on every SQL engine.

SELECT
  product_id,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_revenue,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_revenue,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3_revenue,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4_revenue
FROM quarterly_sales
GROUP BY product_id;

Grouping continuous values into discrete buckets is a daily task in analytics engineering. CASE WHEN lets you define custom bucket boundaries. This is cleaner than width_bucket() because you control the labels and can use irregular intervals that match business logic.

SELECT
  CASE
    WHEN order_total < 25 THEN 'small'
    WHEN order_total < 100 THEN 'medium'
    WHEN order_total < 500 THEN 'large'
    ELSE 'enterprise'
  END AS order_tier,
  COUNT(*) AS order_count,
  ROUND(AVG(order_total), 2) AS avg_total
FROM orders
GROUP BY
  CASE
    WHEN order_total < 25 THEN 'small'
    WHEN order_total < 100 THEN 'medium'
    WHEN order_total < 500 THEN 'large'
    ELSE 'enterprise'
  END
ORDER BY MIN(order_total);

CASE WHEN pairs with IS NULL to handle NULLs explicitly, giving you more control than COALESCE when the logic is not a simple default replacement. You can route NULL rows to different processing paths, flag data quality issues, or apply different calculations based on whether a value is present.

SELECT
  user_id,
  CASE
    WHEN email IS NULL AND phone IS NULL THEN 'unreachable'
    WHEN email IS NULL THEN 'phone_only'
    WHEN phone IS NULL THEN 'email_only'
    ELSE 'fully_contactable'
  END AS contact_status,
  CASE
    WHEN last_login IS NULL THEN 'never_logged_in'
    WHEN last_login < CURRENT_DATE - INTERVAL '90 days' THEN 'dormant'
    WHEN last_login < CURRENT_DATE - INTERVAL '30 days' THEN 'at_risk'
    ELSE 'active'
  END AS engagement_tier
FROM users;

Custom sort orders that don't follow alphabetical or numeric sequence require CASE in ORDER BY. This is common when business stakeholders want priority-based ordering: critical items first, then high, then medium, then low. You assign a numeric rank to each category inside CASE.

SELECT
  ticket_id,
  subject,
  priority,
  created_at
FROM support_tickets
WHERE status = 'open'
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high' THEN 2
    WHEN 'medium' THEN 3
    WHEN 'low' THEN 4
    ELSE 5
  END,
  created_at ASC;

CASE in WHERE lets you apply different filter conditions based on a parameter or another column's value. This pattern appears in stored procedures and parameterized reports where the filter logic changes based on user input. It replaces dynamic SQL in many cases.

-- Filter by different date columns based on report_type
SELECT
  order_id,
  customer_id,
  order_date,
  ship_date,
  status
FROM orders
WHERE
  CASE
    WHEN 'shipped' = 'shipped' THEN ship_date
    WHEN 'shipped' = 'placed' THEN order_date
    ELSE order_date
  END >= CURRENT_DATE - INTERVAL '30 days';