Pivot With Row Totals — Solution & ExplanationProblems / Pivot With Row Totals / EditorialApproach

Conditional aggregation per column. SUM(CASE WHEN category = X THEN amount ELSE 0 END) — the manual pivot idiom. ELSE 0 (rather than the implicit NULL) makes empty cells 0.00, which a finance report expects.

The total is a free rider. SUM(amount) in the same GROUP BY covers all categories; no second query, no join, and it is arithmetically guaranteed to equal the sum of the pivoted columns.

Round at the end, order by region.

Key InsightRow totals expose whether a pivot is *self-consistent*. Computing totals in a separate query (or worse, summing the pivoted columns in a wrapper query after each was rounded) invites drift — a category misspelled in one CASE quietly leaks out of its column but stays in the separate total. One GROUP BY producing both the parts and the whole makes the invariant parts sum to total structural rather than hoped-for.Hints

Each output column is a conditional sum: SUM(CASE WHEN category = '...' THEN amount ELSE 0 END). The ELSE 0 is what turns 'no sales in that category' into 0.00 instead of NULL.

The row total needs no CASE at all — SUM(amount) over the same group already spans every category, and it's guaranteed to equal the sum of the three pivoted columns.

All four aggregates live in one GROUP BY region — resist the urge to pivot first and re-join a totals query.

Solve Pivot With Row Totals yourself →