Combining Cube and Rollup Operations

In this lesson, you will learn how to synthesize complex, multi-dimensional summaries by combining ROLLUP and CUBE into a single, powerful aggregation query.

The Power of Combined Aggregation

Up until now, you have used ROLLUP to create hierarchical subtotals (like total sales per region, then per country) and CUBE to generate every possible combination of grouping (like sales by every permutation of region and product).

Sometimes, you need a hybrid approach. You might want the hierarchical "drill-down" structure of ROLLUP for some columns, but the full "grid-view" exploration of CUBE for others. You can achieve this by using the GROUP BY clause with both operators separated by commas.

Putting It Into Practice

Imagine you have a sales table and you want to see total revenue grouped by Region, Country, and Product. If you want a ROLLUP for the geography (Region -> Country) but want to see the cross-tabulation for Product as well, you can write:

SELECT 
    region, 
    country, 
    product, 
    SUM(sales_amount) AS total_revenue
FROM sales
GROUP BY ROLLUP(region, country), CUBE(product);

What happens behind the scenes?

When you combine these, the database engine creates a Cartesian product of the grouping sets:

1. The ROLLUP(region, country) part generates: (region, country), (region), and () (the grand total).
2. The CUBE(product) part generates: (product) and () (all products combined).

By combining them, the engine effectively multiplies these sets. It calculates sales for every geographical hierarchy, and for each of those, it breaks them down by both specific products and the product total. This allows you to generate massive, high-level summary reports that would otherwise require multiple separate queries or complex UNION ALL statements.

This pattern is essential when you need to provide a single table that allows a user to "zoom out" from specific product sales into regional performance, while simultaneously maintaining the ability to see product totals regardless of location.

Key Takeaway: Combining ROLLUP and CUBE allows you to define complex, multi-dimensional summary logic in a single pass, creating a sophisticated grid of subtotals and cross-totals that standard grouping cannot achieve.