Summary: in this tutorial, you will learn how to use the MySQL ROLLUP
clause to generate subtotals and grand totals.
Setting up a sample table
The following statement creates a new table named sales
that stores the order values summarized by product lines and years. The data comes from the products
, orders
, and orderDetails
tables in the sample database.
CREATE TABLE sales
SELECT
productLine,
YEAR(orderDate) orderYear,
SUM(quantityOrdered * priceEach) orderValue
FROM
orderDetails
INNER JOIN
orders USING (orderNumber)
INNER JOIN
products USING (productCode)
GROUP BY
productLine ,
YEAR(orderDate);
Code language: SQL (Structured Query Language) (sql)
The following query returns all rows from the sales
table:
SELECT * FROM sales;
Code language: SQL (Structured Query Language) (sql)
Output:
+------------------+-----------+------------+
| productLine | orderYear | orderValue |
+------------------+-----------+------------+
| Classic Cars | 2003 | 1374832.22 |
| Classic Cars | 2004 | 1763136.73 |
| Classic Cars | 2005 | 715953.54 |
| Motorcycles | 2003 | 348909.24 |
| Motorcycles | 2004 | 527243.84 |
| Motorcycles | 2005 | 245273.04 |
| Planes | 2003 | 309784.20 |
| Planes | 2004 | 471971.46 |
| Planes | 2005 | 172881.88 |
| Ships | 2003 | 222182.08 |
| Ships | 2004 | 337326.10 |
| Ships | 2005 | 104490.16 |
| Trains | 2003 | 65822.05 |
| Trains | 2004 | 96285.53 |
| Trains | 2005 | 26425.34 |
| Trucks and Buses | 2003 | 376657.12 |
| Trucks and Buses | 2004 | 465390.00 |
| Trucks and Buses | 2005 | 182066.45 |
| Vintage Cars | 2003 | 619161.48 |
| Vintage Cars | 2004 | 854551.85 |
| Vintage Cars | 2005 | 323846.30 |
+------------------+-----------+------------+
21 rows in set (0.00 sec)
Code language: JavaScript (javascript)
MySQL ROLLUP Overview
A grouping set is a set of columns to which you want to group. For example, the following query creates a grouping set denoted by (productline)
SELECT
productline,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline;
Code language: SQL (Structured Query Language) (sql)
Output:
+------------------+-----------------+
| productline | totalOrderValue |
+------------------+-----------------+
| Classic Cars | 3853922.49 |
| Motorcycles | 1121426.12 |
| Planes | 954637.54 |
| Ships | 663998.34 |
| Trains | 188532.92 |
| Trucks and Buses | 1024113.57 |
| Vintage Cars | 1797559.63 |
+------------------+-----------------+
7 rows in set (0.00 sec)
Code language: JavaScript (javascript)
The following query creates an empty grouping set denoted by the ()
:
SELECT
SUM(orderValue) totalOrderValue
FROM
sales;
Code language: SQL (Structured Query Language) (sql)
Output:
+-----------------+
| totalOrderValue |
+-----------------+
| 9604190.61 |
+-----------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
If you want to generate two or more grouping sets together in one query, you may use the UNION ALL
operator as follows:
SELECT
productline,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline
UNION ALL
SELECT
NULL,
SUM(orderValue) totalOrderValue
FROM
sales;
Code language: SQL (Structured Query Language) (sql)
Here’s the query output:
+------------------+-----------------+
| productline | totalOrderValue |
+------------------+-----------------+
| Classic Cars | 3853922.49 |
| Motorcycles | 1121426.12 |
| Planes | 954637.54 |
| Ships | 663998.34 |
| Trains | 188532.92 |
| Trucks and Buses | 1024113.57 |
| Vintage Cars | 1797559.63 |
| NULL | 9604190.61 |
+------------------+-----------------+
8 rows in set (0.00 sec)
Code language: JavaScript (javascript)
Because the UNION ALL
requires all queries to have the same number of columns, we added NULL
in the select list of the second query to fulfill this requirement.
The NULL
in the productLine
column identifies the total super-aggregate line.
This query can generate the total order values by product lines and also the grand total row. However, it has two problems:
- The query is quite lengthy.
- The performance of the query may not be good since the database engine has to internally execute two separate queries and combine the result sets into one.
To fix these issues, you can use the ROLLUP
clause.
The ROLLUP
clause is an extension of the GROUP BY
clause with the following syntax:
SELECT
select_list
FROM
table_name
GROUP BY
c1, c2, c3 WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)
The ROLLUP
generates multiple grouping sets based on the columns or expressions specified in the GROUP BY
clause. For example:
SELECT
productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
+------------------+-----------------+
| productLine | totalOrderValue |
+------------------+-----------------+
| Classic Cars | 3853922.49 |
| Motorcycles | 1121426.12 |
| Planes | 954637.54 |
| Ships | 663998.34 |
| Trains | 188532.92 |
| Trucks and Buses | 1024113.57 |
| Vintage Cars | 1797559.63 |
| NULL | 9604190.61 |
+------------------+-----------------+
8 rows in set (0.00 sec)
Code language: JavaScript (javascript)
As clearly shown in the output, the ROLLUP
clause generates not only the subtotals but also the grand total of the order values.
If you have more than one column specified in the GROUP BY
clause, the ROLLUP
clause assumes a hierarchy among the input columns.
For example:
GROUP BY c1, c2, c3 WITH ROLLUP
Code language: SQL (Structured Query Language) (sql)
The ROLLUP
assumes that there is the following hierarchy:
c1 > c2 > c3
Code language: SQL (Structured Query Language) (sql)
It generates the following grouping sets:
(c1, c2, c3)
(c1, c2)
(c1)
()
Code language: SQL (Structured Query Language) (sql)
If you have two columns specified in the GROUP BY
clause:
GROUP BY c1, c2 WITH ROLLUP
Code language: SQL (Structured Query Language) (sql)
then the ROLLUP
generates the following grouping sets:
(c1, c2)
(c1)
()
Code language: SQL (Structured Query Language) (sql)
See the following query example:
SELECT
productLine,
orderYear,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline,
orderYear
WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
+------------------+-----------+-----------------+
| productLine | orderYear | totalOrderValue |
+------------------+-----------+-----------------+
| Classic Cars | 2003 | 1374832.22 |
| Classic Cars | 2004 | 1763136.73 |
| Classic Cars | 2005 | 715953.54 |
| Classic Cars | NULL | 3853922.49 |
| Motorcycles | 2003 | 348909.24 |
| Motorcycles | 2004 | 527243.84 |
| Motorcycles | 2005 | 245273.04 |
| Motorcycles | NULL | 1121426.12 |
| Planes | 2003 | 309784.20 |
| Planes | 2004 | 471971.46 |
| Planes | 2005 | 172881.88 |
| Planes | NULL | 954637.54 |
| Ships | 2003 | 222182.08 |
| Ships | 2004 | 337326.10 |
| Ships | 2005 | 104490.16 |
| Ships | NULL | 663998.34 |
| Trains | 2003 | 65822.05 |
| Trains | 2004 | 96285.53 |
| Trains | 2005 | 26425.34 |
| Trains | NULL | 188532.92 |
| Trucks and Buses | 2003 | 376657.12 |
| Trucks and Buses | 2004 | 465390.00 |
| Trucks and Buses | 2005 | 182066.45 |
| Trucks and Buses | NULL | 1024113.57 |
| Vintage Cars | 2003 | 619161.48 |
| Vintage Cars | 2004 | 854551.85 |
| Vintage Cars | 2005 | 323846.30 |
| Vintage Cars | NULL | 1797559.63 |
| NULL | NULL | 9604190.61 |
+------------------+-----------+-----------------+
29 rows in set (0.00 sec)
Code language: PHP (php)
The ROLLUP
generates the subtotal row every time the product line changes and the grand total at the end of the result.
The hierarchy in this case is:
productLine > orderYear
Code language: SQL (Structured Query Language) (sql)
If you reverse the hierarchy, for example:
SELECT
orderYear,
productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
orderYear,
productline
WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)
Output:
+-----------+------------------+-----------------+
| orderYear | productLine | totalOrderValue |
+-----------+------------------+-----------------+
| 2003 | Classic Cars | 1374832.22 |
| 2003 | Motorcycles | 348909.24 |
| 2003 | Planes | 309784.20 |
| 2003 | Ships | 222182.08 |
| 2003 | Trains | 65822.05 |
| 2003 | Trucks and Buses | 376657.12 |
| 2003 | Vintage Cars | 619161.48 |
| 2003 | NULL | 3317348.39 |
| 2004 | Classic Cars | 1763136.73 |
| 2004 | Motorcycles | 527243.84 |
| 2004 | Planes | 471971.46 |
| 2004 | Ships | 337326.10 |
| 2004 | Trains | 96285.53 |
| 2004 | Trucks and Buses | 465390.00 |
| 2004 | Vintage Cars | 854551.85 |
| 2004 | NULL | 4515905.51 |
| 2005 | Classic Cars | 715953.54 |
| 2005 | Motorcycles | 245273.04 |
| 2005 | Planes | 172881.88 |
| 2005 | Ships | 104490.16 |
| 2005 | Trains | 26425.34 |
| 2005 | Trucks and Buses | 182066.45 |
| 2005 | Vintage Cars | 323846.30 |
| 2005 | NULL | 1770936.71 |
| NULL | NULL | 9604190.61 |
+-----------+------------------+-----------------+
25 rows in set (0.00 sec)
Code language: PHP (php)
The ROLLUP
generates the subtotal every time the year changes and the grand total at the end of the result set.
The hierarchy in this example is:
orderYear > productLine
Code language: SQL (Structured Query Language) (sql)
The GROUPING() function
To check whether NULL
in the result set represents the subtotals or grand totals, you use the GROUPING()
function.
The GROUPING()
function returns 1 when NULL
occurs in a supper-aggregate row, otherwise, it returns 0.
The GROUPING()
function can be used in the select list, HAVING
clause, and (as of MySQL 8.0.12 ) ORDER BY
clause.
Consider the following query:
SELECT
orderYear,
productLine,
SUM(orderValue) totalOrderValue,
GROUPING(orderYear),
GROUPING(productLine)
FROM
sales
GROUP BY
orderYear,
productline
WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)
Output:
+-----------+------------------+-----------------+---------------------+-----------------------+
| orderYear | productLine | totalOrderValue | GROUPING(orderYear) | GROUPING(productLine) |
+-----------+------------------+-----------------+---------------------+-----------------------+
| 2003 | Classic Cars | 1374832.22 | 0 | 0 |
| 2003 | Motorcycles | 348909.24 | 0 | 0 |
| 2003 | Planes | 309784.20 | 0 | 0 |
| 2003 | Ships | 222182.08 | 0 | 0 |
| 2003 | Trains | 65822.05 | 0 | 0 |
| 2003 | Trucks and Buses | 376657.12 | 0 | 0 |
| 2003 | Vintage Cars | 619161.48 | 0 | 0 |
| 2003 | NULL | 3317348.39 | 0 | 1 |
| 2004 | Classic Cars | 1763136.73 | 0 | 0 |
| 2004 | Motorcycles | 527243.84 | 0 | 0 |
| 2004 | Planes | 471971.46 | 0 | 0 |
| 2004 | Ships | 337326.10 | 0 | 0 |
| 2004 | Trains | 96285.53 | 0 | 0 |
| 2004 | Trucks and Buses | 465390.00 | 0 | 0 |
| 2004 | Vintage Cars | 854551.85 | 0 | 0 |
| 2004 | NULL | 4515905.51 | 0 | 1 |
| 2005 | Classic Cars | 715953.54 | 0 | 0 |
| 2005 | Motorcycles | 245273.04 | 0 | 0 |
| 2005 | Planes | 172881.88 | 0 | 0 |
| 2005 | Ships | 104490.16 | 0 | 0 |
| 2005 | Trains | 26425.34 | 0 | 0 |
| 2005 | Trucks and Buses | 182066.45 | 0 | 0 |
| 2005 | Vintage Cars | 323846.30 | 0 | 0 |
| 2005 | NULL | 1770936.71 | 0 | 1 |
| NULL | NULL | 9604190.61 | 1 | 1 |
+-----------+------------------+-----------------+---------------------+-----------------------+
25 rows in set (0.00 sec)
Code language: PHP (php)
The GROUPING(orderYear)
returns 1 when NULL
in the orderYear
column occurs in a super-aggregate row, 0 otherwise.
Similarly, the GROUPING(productLine)
returns 1 when NULL
in the productLine
column occurs in a super-aggregate row, 0 otherwise.
We often use GROUPING()
function to substitute meaningful labels for super-aggregate NULL
values instead of displaying it directly.
The following example shows how to combine the IF()
function with the GROUPING()
function to substitute labels for the super-aggregate NULL
values in orderYear
and productLine
columns:
SELECT
IF(GROUPING(orderYear),
'All Years',
orderYear) orderYear,
IF(GROUPING(productLine),
'All Product Lines',
productLine) productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
orderYear ,
productline
WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)
The output is:
+-----------+-------------------+-----------------+
| orderYear | productLine | totalOrderValue |
+-----------+-------------------+-----------------+
| 2003 | Classic Cars | 1374832.22 |
| 2003 | Motorcycles | 348909.24 |
| 2003 | Planes | 309784.20 |
| 2003 | Ships | 222182.08 |
| 2003 | Trains | 65822.05 |
| 2003 | Trucks and Buses | 376657.12 |
| 2003 | Vintage Cars | 619161.48 |
| 2003 | All Product Lines | 3317348.39 |
| 2004 | Classic Cars | 1763136.73 |
| 2004 | Motorcycles | 527243.84 |
| 2004 | Planes | 471971.46 |
| 2004 | Ships | 337326.10 |
| 2004 | Trains | 96285.53 |
| 2004 | Trucks and Buses | 465390.00 |
| 2004 | Vintage Cars | 854551.85 |
| 2004 | All Product Lines | 4515905.51 |
| 2005 | Classic Cars | 715953.54 |
| 2005 | Motorcycles | 245273.04 |
| 2005 | Planes | 172881.88 |
| 2005 | Ships | 104490.16 |
| 2005 | Trains | 26425.34 |
| 2005 | Trucks and Buses | 182066.45 |
| 2005 | Vintage Cars | 323846.30 |
| 2005 | All Product Lines | 1770936.71 |
| All Years | All Product Lines | 9604190.61 |
+-----------+-------------------+-----------------+
In this tutorial, you have learned how to use the MySQL ROLLUP()
to generate multiple grouping sets considering a hierarchy between columns specified in the GROUP BY
clause.