Introduction To The SQL Server Rollup

Introduction to the SQL Server ROLLUP

The SQL Server ROLLUP is a subclause of the GROUP BY clause which provides a shorthand for defining multiple grouping sets. Unlike the CUBE subclause, ROLLUP does not create all possible grouping sets based on the dimension columns; the CUBE makes a subset of those.

When generating the grouping sets, ROLLUP assumes a hierarchy among the dimension columns and only generates grouping sets based on this hierarchy.

The ROLLUP is often used to generate subtotals and totals for reporting purposes.

Let’s consider an example. The following CUBE (d1,d2,d3) defines eight possible grouping sets:

1 2 3 4 5 6 7 8

(d1, d2, d3) (d1, d2) (d2, d3) (d1, d3) (d1) (d2) (d3) ()

And the ROLLUP(d1,d2,d3) creates only four grouping sets, assuming the hierarchy d1 > d2 > d3, as follows:

1 2 3 4

(d1, d2, d3) (d1, d2) (d1) ()

The ROLLUP is commonly used to calculate the aggregates of hierarchical data such as sales by year > quarter > month.

SQL Server ROLLUP syntax

The general syntax of the SQL Server ROLLUP is as follows:

1 2 3 4 5 6 7 8 9

SELECT     d1,     d2,     d3,     aggregate_function(c4) FROM     table_name GROUP BY     ROLLUP (d1, d2, d3);

In this syntax, d1, d2, and d3 are the dimension columns. The statement will calculate the aggregation of values in the column c4 based on the hierarchy d1 > d2 > d3.

You can also do a partial roll up to reduce the subtotals generated by using the following syntax:

1 2 3 4 5 6 7 8 9 10

SELECT     d1,     d2,     d3,     aggregate_function(c4) FROM     table_name GROUP BY     d1,     ROLLUP (d2, d3);

SQL Server ROLLUP examples

We will reuse the sales.sales_summary table created in the GROUPING SETS tutorial for the demonstration. If you have not created the sales.sales_summary table, you can use the following statement to create it.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SELECT     b.brand_name AS brand,     c.category_name AS category,     p.model_year,     round(         SUM (             quantity * i.list_price * (1 - discount)         ),         0     ) sales INTO sales.sales_summary FROM     sales.order_items i INNER JOIN production.products p ON p.product_id = i.product_id INNER JOIN production.brands b ON b.brand_id = p.brand_id INNER JOIN production.categories c ON c.category_id = p.category_id GROUP BY     b.brand_name,     c.category_name,     p.model_year ORDER BY     b.brand_name,     c.category_name,     p.model_year;

The following query uses the ROLLUP to calculate the sales amount by brand (subtotal) and both brand and category (total).

1 2 3 4 5 6 7 8

SELECT     brand,     category,     SUM (sales) sales FROM     sales.sales_summary GROUP BY     ROLLUP(brand, category);

Here is the output:

In this example, the query assumes that there is a hierarchy between brand and category, which is the brand > category.

Note that if you change the order of brand and category, the result will be different as shown in the following query:

1 2 3 4 5 6 7 8

SELECT     category,     brand,     SUM (sales) sales FROM     sales.sales_summary GROUP BY     ROLLUP (category, brand);

In this example, the hierarchy is the brand > segment:

The following statement shows how to perform a partial roll-up:

1 2 3 4 5 6 7 8 9

SELECT     brand,     category,     SUM (sales) sales FROM     sales.sales_summary GROUP BY     brand,     ROLLUP (category);

And the output is:

In this tutorial, you have learned how to use the SQL Server ROLLUP to generate multiple grouping sets with an assumption of a hierarchy of the input columns.