The ROLLUP clause in SQL is an extension of the GROUP BY clause that allows you to create subtotals and a grand total in your result set. It generates summary rows in addition to the grouped data, which is useful for analytical reporting.
Here's a general structure of how to use the ROLLUP clause in a GROUP BY query:
Syntax
SELECT column1, column2, ..., aggregate_function(columnN)
FROM table_name
GROUP BY ROLLUP (column1, column2, ...);
Explanation
- column1, column2, ...: These are the columns you want to group by. The ROLLUP generates subtotals for the specified columns as well as a grand total.
- aggregate_function(columnN): This is the function you want to apply (like SUM(), COUNT(), AVG(), etc.) to the data after grouping.
Example
Suppose you have a sales table (sales) with the following columns: region, product, and amount. If you want to get the total sales amounts grouped by region and product, as well as the subtotals for each region and a grand total, you can use the ROLLUP clause like this:
SELECT region, product, SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP (region, product);
Result Interpretation
In this example, the result set would include:
- The total sales amount for each combination of region and product.
- A summary row for each region with the total sales amount across all products.
- A grand total row showing the total sales amount for all regions and products.
Example Output
If your table sales had the following data:
| region | product | amount |
|--------|---------|--------|
| East | Widget | 100 |
| East | Gadget | 150 |
| West | Widget | 200 |
| West | Gadget | 300 |
The output of the ROLLUP query would be:
| region | product | total_sales |
|--------|---------|-------------|
| East | Widget | 100 |
| East | Gadget | 150 |
| East | NULL | 250 | -- subtotal for East
| West | Widget | 200 |
| West | Gadget | 300 |
| West | NULL | 500 | -- subtotal for West
| NULL | NULL | 750 | -- grand total
In this table, NULL values represent the summary rows where the aggregation is applied. By using ROLLUP, you can effectively summarize your data in a meaningful way.