SQL ROLLUP

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

Introduction to SQL ROLLUP

The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. By using the ROLLUP option, you can use a single query to generate multiple grouping sets.

Note that a grouping set is a set of columns by which you group. For example, a query that returns the inventory by the warehouse,  the grouping set is (warehouse).

SELECT
    warehouse, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    warehouse;Code language: SQL (Structured Query Language) (sql)

For more information about the GROUPING SETS, check it out the grouping sets tutorial.

The following illustrates the basic syntax of the SQL ROLLUP:

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table
GROUP BY ROLLUP (c1, c2);
Code language: SQL (Structured Query Language) (sql)

The ROLLUP assumes a hierarchy among the input columns. For example, if the input column is (c1,c2), the hierarchy c1 > c2. The ROLLUP generates all grouping sets that make sense considering this hierarchy. This is why we often use ROLLUP to generate the subtotals and the grand total for reporting purposes.

In the syntax above, ROLLUP(c1,c2) generates three following grouping sets:

(c1,c2)
(c1)
()Code language: SQL (Structured Query Language) (sql)

This syntax is supported by Oracle, Microsoft SQL Server, and PostgreSQL. However, MySQL has a slightly different syntax as shown below:

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table_name
GROUP BY c1, c2 WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)

SQL ROLLUP examples

We will use the inventory table that we set up in the GROUPING SETS tutorial for the demonstration.

SQL ROLLUP with one column example

The following statement uses the GROUP BY clause and the SUM() function to find the total inventory by warehouse:

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY warehouse;
Code language: SQL (Structured Query Language) (sql)

To retrieve the total products in all warehouses, you add the ROLLUP to the GROUP BY clause as follows:

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);
Code language: SQL (Structured Query Language) (sql)

As you can see in the result, the NULL value in the warehouse column specifies the grand total super-aggregate line. In this example, the ROLLUP option causes the query to produce another row that shows the total products in all warehouses.

To make the output more readable, you can use the COALESCE() function to substitute the NULL value by the All warehouses as follows:

SELECT 
    COALESCE(warehouse, 'All warehouses') AS warehouse,
    SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);
Code language: SQL (Structured Query Language) (sql)

SQL ROLLUP with multiple columns example

The following statement calculates the inventory by warehouse and product:

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, product;
Code language: SQL (Structured Query Language) (sql)

Let’s add the ROLLUP to the GROUP BY clause:

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse , product);
Code language: SQL (Structured Query Language) (sql)

Note that the output consists of summary information at two levels of analysis, not just one:

SQL ROLLUP with partial rollup example

You can use ROLLUP to perform a partial roll-up that reduces the number of subtotals calculated as shown in the following example:

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, ROLLUP (product);
Code language: SQL (Structured Query Language) (sql)

In this example, the ROLLUP only makes a supper-aggregate summary for the product column, not the warehouse column.

In this tutorial, you have learned how to use the SQL ROLLUP to perform multiple levels of analysis with a single query.