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
Table: warehouse_stocks
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 –
(warehouse_city, product_category),
(warehouse_city) and
() (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 –
(warehouse_city, product_category, product_brand) which has 10 rows,
(warehouse_city, product_category) which has 4 rows,
(warehouse_city) which has 2 rows and
() (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.