Advanced SQL Aggregation Using ROLLUP, CUBE and Grouping SETS
- Yogalakshmi Prabakaran
- 6 hours ago
- 2 min read
Data aggregation is a crucial aspect of SQL reporting, especially when dealing with large datasets. The standard GROUP BY clause helps summarize data, but when reporting becomes complex with multiple dimensions, advanced aggregation techniques like ROLLUP, CUBE, and GROUPING SETS step in to provide hierarchical and multi-level summaries efficiently.
In this blog, we will dive into these powerful SQL techniques, understand their differences, and apply them in practical scenarios.
Why Advanced Aggregation Matters
Traditional GROUP BY is great for basic grouping, but when handling multi-level reporting (e.g., analyzing revenue by region, product, and year), running multiple queries separately can be inefficient. Advanced grouping techniques allow us to:
Generate hierarchical subtotals (ROLLUP)
Produce all possible aggregations (CUBE)
Customize grouping combinations flexibly (GROUPING SETS)
Understanding ROLLUP
ROLLUP helps generate subtotals and grand totals across defined grouping levels. It’s particularly useful for hierarchical reports. It eliminates the necessity for multiple queries or intricate manual calculations.
Let us consider we have these tables:
regions: North, South, East, West
customers: Assigned to regions
products: With categories like Electronics, Furniture
sales: Transactions that include customer, product, quantity, and total
SQL QUERY: Find sales totals by region and product category, plus subtotals for each region and a grand total.
SELECT
 r.region_name,
 p.category,
 SUM(s.total) AS sales
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN products p ON s.product_id = p.product_id
JOIN regions r ON c.region_id = r.region_id
GROUP BY ROLLUP (r.region_name, p.category)
ORDER BY r.region_name, p.category;
OUTPUT:
Here, ROLLUP(region, product) groups data hierarchically, providing totals at each level. Notice the rows with NULL represent subtotal or total rows.
Using CUBE for Multi-Dimensional Aggregation
CUBE takes aggregation further by computing all possible group combinations, making it ideal for analyzing multiple dimensions.
CUBE(a, b) generates all grouping combinations of a and b:
(a, b)
(a, NULL)
(NULL, b)
(NULL, NULL)
SQL QUERY: For Sales by Region and Product Category
SELECT
r.region_name,
p.category,
 sum(s.total) AS total_sales
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN regions r ON c.region_id = r.region_id
JOIN products p ON s.product_id = p.product_id
GROUP BY CUBE(r.region_name, p.category)
ORDER BY r.region_name, p.category;Â Â Â Â Â Â Â Â
OUTPUT:
Unlike ROLLUP, which aggregates in a hierarchical structure, CUBE generates  all possible grouping combinations.
Using GROUPING SETS for Flexible Custom Aggregations
If you need specific aggregations without computing everything, GROUPING SETS allows you to define custom groups.
SQL QUERY: Customized Aggregations for Region and Product
SELECT
r.region_name,
 p.category,
 SUM(s.total) AS total_sales
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN regions r ON c.region_id = r.region_id
JOIN products p ON s.product_id = p.product_id
GROUP BY GROUPING SETS (
 (r.region_name, p.category), -- Group by region and category
 (p.category),                                  -- Group by category only
 ()                                                          -- Grand total
)
ORDER BY r.region_name, p.category;
OUTPUT:
GROUPING SETS provides custom multi-level aggregation in one query.
Key Differences: ROLLUP vs. CUBE vs. GROUPING SETS
Technique | Best For | Example Use Case |
ROLLUP | Hierarchical aggregation | Revenue by region → region subtotal → grand total |
CUBE | All possible aggregations | Sales summary by region and product across all groups |
GROUPING SETS | Custom aggregation combinations | Selective revenue analysis by region/product |
Â
Advanced SQL aggregation techniques like ROLLUP, CUBE, and GROUPING SETS improve reporting efficiency, reduce complexity, and enhance decision-making in data analytics. By mastering these methods, data professionals can create dynamic, multi-level reports effortlessly.
Links and references: