In SQL, the CUBE and ROLLUP operators are used in conjunction with the GROUP BY clause to generate summarized results. These operators help produce cross-tabular reports with subtotals and grand totals, making it easier to analyze data across multiple dimensions.
ROLLUP Operator
The ROLLUP operator is used to create subtotals along with a grand total. When applied, it generates the aggregates for the specified columns, and at each level of aggregation, it provides a subtotal.
Example of ROLLUP
Consider a sales table (Sales) with columns for Region, Product, and SalesAmount. Here’s how to generate a report that includes totals by product within each region and a grand total:
SELECT
Region,
Product,
SUM(SalesAmount) AS TotalSales
FROM
Sales
GROUP BY
ROLLUP (Region, Product);
In this query:
- The first level of aggregation groups by Region and Product.
- A subtotal by Product within each Region is computed.
- The second level of aggregation (the grand total) is computed across all regions and products.
CUBE Operator
The CUBE operator is more powerful as it generates subtotals for all possible combinations of the specified columns, creating a complete cross-tabulation.
Example of CUBE
Using the same sales scenario, if you want all possible combinations of subtotals:
SELECT
Region,
Product,
SUM(SalesAmount) AS TotalSales
FROM
Sales
GROUP BY
CUBE (Region, Product);
In this query:
- The result includes subtotals for all combinations of Region and Product.
- Subtotals by each Region (summing across products) and subtotals by Product (summing across regions) plus the grand total are computed.
Result Interpretation
Both operators include NULL values in the aggregated results to indicate subtotals and grand totals:
- In the result set from ROLLUP, a NULL in the Product column indicates a subtotal for that Region.
- A total with NULL in both Region and Product indicates the grand total across all dimensions.
Choosing Between ROLLUP and CUBE
- Use ROLLUP when you need a hierarchical subtotal of aggregations, typically where data is organized in a hierarchy (like Regions and then Products within Regions).
- Use CUBE when you want complete cross-tabulations that provide subtotals for every possible combination of the dimensions.
Conclusion
Using ROLLUP and CUBE can greatly simplify querying for summary reports in SQL, enabling efficient data analysis and reporting without needing multiple individual queries.