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:

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:

The following statement shows how to perform a partial roll-up:
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.
0 Comments