Conditional aggregation with CASE

CASE basics

CASE WHEN total > 500 THEN 'large'
     WHEN total > 100 THEN 'medium'
     ELSE 'small'
END

Conditions are evaluated top to bottom and the first match wins, so order matters. Without ELSE, unmatched rows return null.

CASE inside an aggregate

This is the pattern worth memorising:

SELECT region,
       COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'shipped' THEN 1 END)      AS shipped,
       SUM(CASE WHEN status = 'refunded' THEN total ELSE 0 END) AS refunded_value
FROM orders
GROUP BY region;

Note the missing ELSE in the COUNT. Unmatched rows return null, and COUNT skips nulls — which is exactly what makes it count only the matches. Adding ELSE 0 there would count everything, since zero is not null.

Pivoting rows into columns

The same trick turns row values into columns:

SELECT product,
       SUM(CASE WHEN year = 2023 THEN revenue ELSE 0 END) AS rev_2023,
       SUM(CASE WHEN year = 2024 THEN revenue ELSE 0 END) AS rev_2024
FROM sales
GROUP BY product;

One pass, one query, instead of two queries joined together.

The FILTER alternative

PostgreSQL and SQLite offer a clearer syntax for the same thing:

COUNT(*) FILTER (WHERE status = 'shipped')

Use it where available; CASE is the portable fallback.