In this lesson, you will learn how to generate complex, hierarchical summary reports in a single query using ROLLUP and GROUPING SETS.
So far, you’ve used GROUP BY to collapse rows into a single level of detail. But what if your manager asks for a report that shows total sales by Region, by Category, and a grand total for the entire company? Previously, you might have written three separate queries and combined them. Advanced grouping lets the database engine calculate these different dimensions at once.
Using ROLLUP
The ROLLUP operator creates a hierarchy. If you group by (region, category), a ROLLUP will give you:
- Sales by Region and Category.
- Subtotals for each Region.
- A Grand Total.
SELECT region, category, SUM(sales)
FROM orders
GROUP BY ROLLUP(region, category);
The database automatically inserts NULL values to represent the "all categories" or "all regions" rows. These rows represent the subtotal or grand total.
Using GROUPING SETS
If you don’t need the full hierarchy but want to see specific combinations—like total sales per Region and total sales per Category—GROUPING SETS allows you to pick exactly which "slices" of data to aggregate.
SELECT region, category, SUM(sales)
FROM orders
GROUP BY GROUPING SETS (
(region),
(category)
);
Unlike ROLLUP, this won't create a grand total unless you explicitly add () as a grouping set. It is a precise way to generate a summary report that looks at the same data through different lenses without performing multiple scans of your table.
These strategies are powerful because they allow you to create "multi-dimensional" reports. Instead of grouping by one column at a time, you are asking the database to organize the data into a nested structure that is ready for immediate presentation.
Key Takeaway: Use ROLLUP for hierarchical subtotals and GROUPING SETS for custom, non-hierarchical combinations to generate complex summary reports in one efficient step.