Multi-Level Aggregation Report — Solution & ExplanationProblems / Multi-Level Aggregation Report / EditorialApproachMulti-level aggregation produces summary rows at different granularities in a single result set.UNION ALL Strategy

Detail query: GROUP BY region, category — finest grain.

Subtotal query: GROUP BY region — one level up, with 'ALL' as the category placeholder.

Grand total query: No GROUP BY — single row, both dimensions set to 'ALL'.

Combine with UNION ALL and add a level label to distinguish the aggregation level.Alternative: GROUPING SETSDuckDB supports:```sqlSELECTCOALESCE(region, 'ALL') AS region,COALESCE(category, 'ALL') AS category,SUM(amount) AS total_amountFROM store_salesGROUP BY GROUPING SETS ((region, category),(region),())```GROUPING SETS is more concise but less explicit about the aggregation semantics. The UNION ALL approach is clearer for learning.Hints

Use UNION ALL to combine three separate GROUP BY queries at different granularities.

Use string literals like 'ALL' as placeholder values for aggregated-away dimensions.

DuckDB also supports GROUPING SETS and ROLLUP for this pattern, but UNION ALL is more explicit.

Solve Multi-Level Aggregation Report yourself →