Hierarchical Aggregation using the ROLLUP Extension

Snippet

Hierarchical Aggregation using the ROLLUP Extension

ROLLUP is an extension of the GROUP BY clause that produces subtotal rows in addition to the regular grouped rows. It creates a hierarchy of aggregates, moving from the most detailed level (region and city) up to a grand total, which is essential for reporting and OLAP operations.

snippet.sql
sql
SELECT region, city, SUM(revenue) 
FROM sales_data 
GROUP BY ROLLUP (region, city);

Breakdown

SELECT region, city, SUM(revenue)

Defines the dimensions and the measure to be aggregated.

GROUP BY ROLLUP (region, city)

Specifies the hierarchy for subtotal generation (Region > City > Grand Total).

Previous snippet Next snippet

More Expert SQL exercises →

From your library

Hierarchical Aggregation using the ROLLUP Extension

Related