SQL Server ROLLUP

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use ROLLUP to aggregate different groups along with subtotals and grand total.

What is ROLLUP in SQL Server?

The ROLLUP operator is an extension of the GROUPING SET operator just like the GROUPING SET operator is an extension of the GROUP BY operator. The GROUP BY operator aggregates a single group. GROUPING SET aggregates multiple groups by allowing us to specify a set of groups in one query. ROLLUP also aggregates multiple groups with a single query but without the need to specify groups explicitly and additionally provides subtotals and grand total. It creates the groups automatically using the hierarchical relation between the specified correlated data columns.

SQL Server ROLLUP Syntax

The basic syntax of the ROLLUP operator is given below.

SELECT
column1, column2, aggregate_function (column 3)
FROM
table
GROUP BY ROLLUP (column1, column2);

In this syntax,

• column – a column from the table in the SELECT list.
• aggregate_function (column) – column on which aggregate function (i.e. SUM, COUNT, AVG etc.) is used.  
• GROUP BY – SQL keyword combination to specify a column or multiple columns to create a group on which the to apply the aggregate.
• ROLLUP – SQL keyword combination which creates multiple groups (i.e. grouping set) and applies the aggregate on them.

SQL Server ROLLUP with Examples

Let us try to understand when and how to use the ROLLUP operator in a SQL query. Suppose we are an electronics retail chain (primarily dealing in laptops and tablets) having outlets across the country. These outlets are supplied by warehouses in the major cities and state capitals. For the North we have warehouses in Kolkata and Delhi. In the company database we have a table called warehouse_stocks which stores the stock information in the two warehouses. The table is represented below. We will use this sample table for reference in our examples.

warehouse_city	product_category	product_brand	stock_quantity
Delhi	Laptop	Toshiba	2000
Delhi	Laptop	Dell	7000
Delhi	Laptop	HP	2500
Delhi	Tablet	Sony	3500
Delhi	Tablet	Samsung	4200
Kolkata	Laptop	HP	3000
Kolkata	Laptop	Dell	4000
Kolkata	Laptop	Toshiba	1000
Kolkata	Tablet	Sony	3000
Kolkata	Tablet	Samsung	4000

Using the above table, we will analyze the inventory using GROUP BY, GROUPING SET and ROLLUP. It will help us understand the differences between the different operators and the appropriate use case of the ROLLUP operator.

1) Single column example

First, we will use the GROUP BY operator. The following query does the same on a single column group (warehouse). The sum of stock quantity is aliased as stock in the query.

SELECT
warehouse_city,
SUM (stock_quantity) stock
FROM warehouse_stocks
GROUP BY (warehouse_city);

The resultset of the above query is the following. It shows the total stock of both laptops and tablets in each warehouse.

warehouse_city	stock
Delhi	19200
Kolkata	15000

Now let us execute the same above query using GROUPING SETS. The following query does the same.

SELECT
warehouse_city,
SUM (stock_quantity) stock
FROM warehouse_stocks
GROUP BY
GROUPING SETS (warehouse_city);

It produces the same resultset as the previous query. that is because it is a one column group (warehouse_city) and not really a set of groups.

warehouse_city	stock
Delhi	19200
Kolkata	15000

Now we will apply ROLLUP to the query and see the difference. The following query uses ROLLUP on the same column (warehouse_city).

SELECT
warehouse_city,
SUM (stock_quantity) stock
FROM warehouse_stocks
GROUP BY
ROLLUP (warehouse_city);

We can see that the output is different this time. It has 3 rows with an additional row showing the total combined stock from both warehouses. This is basically the output of the empty set () and the grand total of the group.

warehouse_city	stock
Delhi	19200
Kolkata	15000
NULL	34200

2) Double column example

We will now add one more single column group to the GROUPING SETS query. The following query uses GROUPING SETS to aggregate two single column groups (warehouse_city) and (product_category). (Please note that we cannot use GROUP BY anymore as using GROUP BY we can aggregate only one group.)

SELECT
warehouse_city,
product_category,
SUM (stock_quantity) stock
FROM warehouse_stocks
GROUP BY
GROUPING SETS ((warehouse_city), (product_category));

It will produce the following resultset. The first 2 columns are aggregates for product_category and the second 2 columns for warehouse_city. Columns which are not part of the group show NULL in their column values.

warehouse_city	product_category	stock
NULL	Laptop	19500
NULL	Tablet	14700
Delhi	NULL	19200
Kolkata	NULL	15000

We will now subject the above query to ROLLUP. The following query does the same.

SELECT
warehouse_city,
product_category,
SUM (stock_quantity) stock
FROM warehouse_stocks
GROUP BY
ROLLUP ((warehouse_city), (product_category));

It will produce the following resultset. We can see that it has 7 rows. This is because rollup creates a set of 3 groups out of the 2 columns specified separately. They are –

1. (warehouse_city, product_category),
2. (warehouse_city) and
3. () (i.e. empty set which returns the grand total of all stock from both warehouses, 19200+15000=34200).

They are highlighted in green, yellow and blue respectively in the table.

This is the way ROLLUP operates. It takes a number of columns as argument and then constructs the groups hierarchically. We do not need to and cannot specify groups explicitly as we did in the GROUPING SETS query.

warehouse_city	product_category	stock
Delhi	Laptop	11500
Delhi	Tablet	7700
Delhi	NULL	19200
Kolkata	Laptop	8000
Kolkata	Tablet	7000
Kolkata	NULL	15000
NULL	NULL	34200

3) Adding all the columns to ROLLUP

In this final example we will add all the 3 columns to the ROLLUP query. The following query does the same.

SELECT warehouse_city, product_category, product_brand, SUM (stock_quantity) stock FROM warehouse_stocks GROUP BY ROLLUP (warehouse_city, product_category, product_brand);

The resultset of the above query is below. It has 17 rows. This is because rollup has created a set of 4 groups out of the columns specified separately. They are –

1. (warehouse_city, product_category, product_brand) which has 10 rows,
2. (warehouse_city, product_category) which has 4 rows,
3. (warehouse_city) which has 2 rows and
4. () (i.e. empty set which returns the grand total of all stock from both warehouses, 19200+15000=34200).

They are highlighted in green, yellow, gray and blue respectively in the table. It means that if we specify n columns in the query ROLLUP creates and reports the aggregates for n+1 sets of groups and it does so by removing one column at a time for each new group it aggregates like listed above.

So, we see that ROLLUP is an advanced option for automatic comprehensive hierarchical data analysis and reporting unlike GROUP BY and GROUPING SETS which is more for specific and custom data analysis on selected dimensions.

warehouse_city	product_category	product_brand	stock
Delhi	Laptop	Dell	7000
Delhi	Laptop	HP	2500
Delhi	Laptop	Toshiba	2000
Delhi	Laptop	NULL	11500
Delhi	Tablet	Samsung	4200
Delhi	Tablet	Sony	3500
Delhi	Tablet	NULL	7700
Delhi	NULL	NULL	19200
Kolkata	Laptop	Dell	4000
Kolkata	Laptop	HP	3000
Kolkata	Laptop	Toshiba	1000
Kolkata	Laptop	NULL	8000
Kolkata	Tablet	Samsung	4000
Kolkata	Tablet	Sony	3000
Kolkata	Tablet	NULL	7000
Kolkata	NULL	NULL	15000
NULL	NULL	NULL	34200