How does the CASE expression work?

There are two forms. The searched form uses arbitrary boolean conditions. The simple form compares one expression against several values. The first matching branch wins, and ELSE supplies the default.

SELECT
  id,
  status,
  CASE
    WHEN status = 'paid' THEN 'complete'
    WHEN status IN ('pending', 'authorized') THEN 'in_progress'
    ELSE 'needs_attention'
  END AS status_group
FROM orders;

Conditional aggregation

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_orders
FROM orders;

• If no branch matches and there is no ELSE, the result is NULL
• Conditions are evaluated in order, so put the most specific first
• CASE is not a control-flow statement and cannot contain statements
• Some engines support FILTER as a cleaner alternative for conditional aggregates