SQL Server ROLLUP

Summary: in this tutorial, you will learn how to use the SQL Server ROLLUP to generate multiple grouping sets.

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:

(d1, d2, d3)
(d1, d2)
(d2, d3)
(d1, d3)
(d1)
(d2)
(d3)
()
Code language: SQL (Structured Query Language) (sql)

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

(d1, d2, d3)
(d1, d2)
(d1)
()
Code language: SQL (Structured Query Language) (sql)

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:

SELECT
    d1,
    d2,
    d3,
    aggregate_function(c4)
FROM
    table_name
GROUP BY
    ROLLUP (d1, d2, d3);
Code language: SQL (Structured Query Language) (sql)

In this syntax, d1, d2, and d3 are 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:

SELECT
    d1,
    d2,
    d3,
    aggregate_function(c4)
FROM
    table_name
GROUP BY
    d1, 
    ROLLUP (d2, d3);
Code language: SQL (Structured Query Language) (sql)

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.

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;Code language: SQL (Structured Query Language) (sql)

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

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    ROLLUP(brand, category);Code language: SQL (Structured Query Language) (sql)

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:

SELECT
    category,
    brand,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    ROLLUP (category, brand);Code language: SQL (Structured Query Language) (sql)

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

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

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    ROLLUP (category);Code language: SQL (Structured Query Language) (sql)

Output:

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.

Was this tutorial helpful?