CASE for Conditional Columns (Pivot)

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

Step-by-Step Breakdown

1. 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.
2. Repeat the pattern for cancelled and pending.
3. GROUP BY p.category: one row per category with three status columns.

Variations

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

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).