case

CASE for Conditional Columns (Pivot)

Use CASE with COUNT to pivot order statuses into separate columns.

Overview

By wrapping CASE inside an aggregate, you can "pivot" row values into columns — turning distinct status values into their own counted columns. This is the SQL equivalent of a spreadsheet pivot and avoids multiple separate queries.

The Query

Store (Customers/Orders/Products)

Loading database engine...

Step-by-Step Breakdown

COUNT(CASE WHEN o.status = 'completed' THEN 1 END) — counts only rows where the condition is true; unmatched rows yield NULL and aren't counted.
Repeat the pattern for cancelled and pending.
GROUP BY p.category — one row per category with three status columns.

COUNT(CASE ...) vs FILTER

This CASE-inside-COUNT idiom works on every SQL engine, unlike the FILTER clause which some (e.g. MySQL) don't support.

Variations

Pivot student pass/fail by term (using grades):

School (Students/Courses/Grades)

Loading database engine...

Common Mistakes

Using SUM instead of COUNT with NULLs. COUNT(CASE … THEN 1 END) ignores the implicit NULL else; SUM(CASE … THEN 1 ELSE 0 END) also works but is more verbose.
Forgetting GROUP BY. Without it, the pivoted columns collapse to one row.
Mismatched THEN types. Keep THEN values consistent (all 1, for example).