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_cityproduct_categoryproduct_brandstock_quantity
DelhiLaptopToshiba2000
DelhiLaptopDell7000
DelhiLaptopHP2500
DelhiTabletSony3500
DelhiTabletSamsung4200
KolkataLaptopHP3000
KolkataLaptopDell4000
KolkataLaptopToshiba1000
KolkataTabletSony3000
KolkataTabletSamsung4000
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_citystock
Delhi19200
Kolkata15000

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_citystock
Delhi19200
Kolkata15000

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_citystock
Delhi19200
Kolkata15000
NULL34200

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_cityproduct_categorystock
NULLLaptop19500
NULLTablet14700
DelhiNULL19200
KolkataNULL15000

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_cityproduct_categorystock
DelhiLaptop11500
DelhiTablet7700
DelhiNULL19200
KolkataLaptop8000
KolkataTablet7000
KolkataNULL15000
NULLNULL34200

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_cityproduct_categoryproduct_brandstock
DelhiLaptopDell7000
DelhiLaptopHP2500
DelhiLaptopToshiba2000
DelhiLaptopNULL11500
DelhiTabletSamsung4200
DelhiTabletSony3500
DelhiTabletNULL7700
DelhiNULLNULL19200
KolkataLaptopDell4000
KolkataLaptopHP3000
KolkataLaptopToshiba1000
KolkataLaptopNULL8000
KolkataTabletSamsung4000
KolkataTabletSony3000
KolkataTabletNULL7000
KolkataNULLNULL15000
NULLNULLNULL34200

Advertisement