Status Count Pivot — Solution & ExplanationProblems / Status Count Pivot / EditorialApproach

Group by region — one output row per region.

For each status, add a conditional aggregate: SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END). The CASE expression scores every row as 1 (matches the status) or 0 (does not), and SUM adds the scores — a per-status count computed inside one pass over the table.

Because of the ELSE 0, every row contributes a number to every column. A region with zero cancelled orders sums a column of 0s and correctly shows 0. (The alternative COUNT(CASE WHEN status = 'cancelled' THEN 1 END) also returns 0, because COUNT ignores the NULLs the CASE produces for non-matching rows.)

Order by region.

Key InsightSUM(CASE WHEN ... THEN 1 ELSE 0 END) is the manual pivot — the most portable way to turn a categorical column's values into separate columns. It needs one expression per known category, all computed in a single scan, with no join or subquery per status. Compare it to filtering: WHERE status = 'cancelled' GROUP BY region would silently *drop* regions that have no cancelled orders, while the conditional aggregate keeps every region and shows an honest 0. Some engines offer PIVOT or FILTER (WHERE ...) syntax as sugar, but the CASE trick works everywhere — SQL, pandas, and Spark alike.Hints

You cannot GROUP BY status here — status must become columns, not rows. This is a pivot, done with one aggregate expression per status value.

SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) scores each row 1 or 0 and adds them up — a count of pending orders that runs inside a single GROUP BY region.

The ELSE 0 is what guarantees a 0 (not NULL) for a region with no orders of that status: every row contributes a number, so SUM never sees an empty/all-NULL input. COUNT(CASE WHEN ... THEN 1 END) works too, since COUNT of no rows is 0.

In PySpark: F.sum(F.when(F.col("status") == "pending", F.lit(1)).otherwise(F.lit(0))).

Solve Status Count Pivot yourself →