The ROLLUP and CUBE operators in SQL are used with the GROUP BY clause to generate subtotals and grand totals for aggregated data. Here’s how each of them works:
ROLLUP
The ROLLUP operator generates a result set that includes subtotals and a grand total for the grouped fields specified. It is useful when you want to roll up the data along a hierarchy.
Syntax:
SELECT column1, column2, SUM(some_column)
FROM your_table
GROUP BY ROLLUP(column1, column2);
Example:
Consider a sales table with the following structure:
| Store | Product | Amount |
|----------|---------|--------|
| Store A | Item 1 | 100 |
| Store A | Item 2 | 150 |
| Store B | Item 1 | 200 |
| Store B | Item 2 | 250 |
If you want to get the total sales for each product at each store, along with the subtotal for each store and a grand total, you could use:
SELECT Store, Product, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY ROLLUP(Store, Product);
Result:
| Store | Product | TotalAmount |
|----------|---------|-------------|
| Store A | Item 1 | 100 |
| Store A | Item 2 | 150 |
| Store A | NULL | 250 | -- Subtotal for Store A
| Store B | Item 1 | 200 |
| Store B | Item 2 | 250 |
| Store B | NULL | 450 | -- Subtotal for Store B
| NULL | NULL | 700 | -- Grand Total
CUBE
The CUBE operator is similar to ROLLUP, but it generates subtotals for all combinations of the specified columns, not just hierarchically. It can be useful for generating multidimensional reports.
Syntax:
SELECT column1, column2, SUM(some_column)
FROM your_table
GROUP BY CUBE(column1, column2);
Example:
Using the same sales table, if you want to generate totals for each store and product along with the totals for each store and product combination, you can use:
SELECT Store, Product, SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY CUBE(Store, Product);
Result:
| Store | Product | TotalAmount |
|----------|---------|-------------|
| Store A | Item 1 | 100 |
| Store A | Item 2 | 150 |
| Store A | NULL | 250 | -- Subtotal for Store A
| Store B | Item 1 | 200 |
| Store B | Item 2 | 250 |
| Store B | NULL | 450 | -- Subtotal for Store B
| NULL | Item 1 | 300 | -- Subtotal for Item 1 across stores
| NULL | Item 2 | 400 | -- Subtotal for Item 2 across stores
| NULL | NULL | 700 | -- Grand Total
Summary
- ROLLUP creates subtotals in a hierarchical manner, meaning it considers the order of columns and produces subtotals from the last column up to the first.
- CUBE creates subtotals for all combinations of the columns, providing a full cross-tabulation of the data.
Both allow you to easily summarize your data without needing multiple queries to obtain subtotals and grand totals.