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:
And the ROLLUP(d1,d2,d3) creates only four grouping sets, assuming the hierarchy d1 > d2 > d3, as follows:
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:
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:

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.
The following query uses the ROLLUP to calculate the sales amount by brand (subtotal) and both brand and category (total).
Here is the output:
SQL Server ROLLUP example
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:
In this example, the hierarchy is the brand > segment:
SQL Server ROLLUP example 2
The following statement shows how to perform a partial roll-up:
And the output is:
SQL Server Partial ROLLUP example
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.

Post a Comment