{"id":7200,"date":"2018-08-31T09:37:03","date_gmt":"2018-08-31T16:37:03","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7200"},"modified":"2023-12-31T02:12:26","modified_gmt":"2023-12-31T09:12:26","slug":"mysql-rollup","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-rollup\/","title":{"rendered":"MySQL ROLLUP"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL ROLLUP<\/code> clause to generate subtotals and grand totals.<\/p>\n\n\n\n

Setting up a sample table<\/h2>\n\n\n\n

The following statement creates a new table<\/a> named sales<\/code> that stores the order values summarized by product lines and years. The data comes from the products<\/code>, orders<\/code>, and orderDetails<\/code> tables in the sample database<\/a>.<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> sales\nSELECT<\/span>\n    productLine,\n    YEAR<\/span>(orderDate) orderYear,\n    SUM<\/span>(quantityOrdered * priceEach) orderValue\nFROM<\/span>\n    orderDetails\n        INNER<\/span> JOIN<\/span>\n    orders USING<\/span> (orderNumber)\n        INNER<\/span> JOIN<\/span>\n    products USING<\/span> (productCode)\nGROUP<\/span> BY<\/span>\n    productLine ,\n    YEAR<\/span>(orderDate);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query returns all rows from the sales<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------------+-----------+------------+\r\n| productLine      | orderYear | orderValue |\r\n+------------------+-----------+------------+\r\n| Classic Cars     |      2003<\/span> | 1374832.22<\/span> |\r\n| Classic Cars     |      2004<\/span> | 1763136.73<\/span> |\r\n| Classic Cars     |      2005<\/span> |  715953.54<\/span> |\r\n| Motorcycles      |      2003<\/span> |  348909.24<\/span> |\r\n| Motorcycles      |      2004<\/span> |  527243.84<\/span> |\r\n| Motorcycles      |      2005<\/span> |  245273.04<\/span> |\r\n| Planes           |      2003<\/span> |  309784.20<\/span> |\r\n| Planes           |      2004<\/span> |  471971.46<\/span> |\r\n| Planes           |      2005<\/span> |  172881.88<\/span> |\r\n| Ships            |      2003<\/span> |  222182.08<\/span> |\r\n| Ships            |      2004<\/span> |  337326.10<\/span> |\r\n| Ships            |      2005<\/span> |  104490.16<\/span> |\r\n| Trains           |      2003<\/span> |   65822.05<\/span> |\r\n| Trains           |      2004<\/span> |   96285.53<\/span> |\r\n| Trains           |      2005<\/span> |   26425.34<\/span> |\r\n| Trucks and Buses |      2003<\/span> |  376657.12<\/span> |\r\n| Trucks and Buses |      2004<\/span> |  465390.00<\/span> |\r\n| Trucks and Buses |      2005<\/span> |  182066.45<\/span> |\r\n| Vintage Cars     |      2003<\/span> |  619161.48<\/span> |\r\n| Vintage Cars     |      2004<\/span> |  854551.85<\/span> |\r\n| Vintage Cars     |      2005<\/span> |  323846.30<\/span> |\r\n+------------------+-----------+------------+\r\n21<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL ROLLUP Overview<\/h2>\n\n\n\n
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)<\/code><\/p>\n\n\n
SELECT<\/span> \n    productline, \n    SUM<\/span>(orderValue) totalOrderValue\nFROM<\/span>\n    sales\nGROUP<\/span> BY<\/span> \n    productline;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------------+-----------------+\r\n| productline      | totalOrderValue |\r\n+------------------+-----------------+\r\n| Classic Cars     |      3853922.49<\/span> |\r\n| Motorcycles      |      1121426.12<\/span> |\r\n| Planes           |       954637.54<\/span> |\r\n| Ships            |       663998.34<\/span> |\r\n| Trains           |       188532.92<\/span> |\r\n| Trucks and Buses |      1024113.57<\/span> |\r\n| Vintage Cars     |      1797559.63<\/span> |\r\n+------------------+-----------------+\r\n7<\/span> rows in<\/span> set<\/span> (0.00 sec)\r\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The following query creates an empty grouping set denoted by the ()<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    SUM<\/span>(orderValue) totalOrderValue\nFROM<\/span>\n    sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------------+\r\n| totalOrderValue |\r\n+-----------------+\r\n|      9604190.61<\/span> |\r\n+-----------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    productline, \n    SUM<\/span>(orderValue) totalOrderValue\nFROM<\/span>\n    sales\nGROUP<\/span> BY<\/span> \n    productline \nUNION<\/span> ALL<\/span>\nSELECT<\/span> \n    NULL<\/span>, \n    SUM<\/span>(orderValue) totalOrderValue\nFROM<\/span>\n    sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here’s the query output:<\/p>\n\n\n
+------------------+-----------------+\r\n| productline      | totalOrderValue |\r\n+------------------+-----------------+\r\n| Classic Cars     |      3853922.49<\/span> |\r\n| Motorcycles      |      1121426.12<\/span> |\r\n| Planes           |       954637.54<\/span> |\r\n| Ships            |       663998.34<\/span> |\r\n| Trains           |       188532.92<\/span> |\r\n| Trucks and Buses |      1024113.57<\/span> |\r\n| Vintage Cars     |      1797559.63<\/span> |\r\n| NULL             |      9604190.61<\/span> |\r\n+------------------+-----------------+\r\n8<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Because the UNION ALL<\/code> requires all queries to have the same number of columns, we added NULL<\/code> in the select list of the second query to fulfill this requirement.<\/p>\n\n\n\n
The NULL<\/code> in the productLine<\/code> column identifies the total super-aggregate line.<\/p>\n\n\n\n
This query can generate the total order values by product lines and also the grand total row. However, it has two problems:<\/p>\n\n\n\n
    \n
  1. The query is quite lengthy.<\/li>\n\n\n\n
  2. 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.<\/li>\n<\/ol>\n\n\n\n
    To fix these issues, you can use the ROLLUP<\/code> clause.<\/p>\n\n\n\n
    The ROLLUP<\/code> clause is an extension of the GROUP BY<\/a><\/code> clause with the following syntax:<\/p>\n\n\n
    SELECT<\/span> \n    select_list\nFROM<\/span> \n    table_name\nGROUP<\/span> BY<\/span>\n    c1, c2, c3 WITH<\/span> ROLLUP<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The ROLLUP<\/code> generates multiple grouping sets based on the columns or expressions specified in the GROUP BY<\/code> clause. For example:<\/p>\n\n\n
    SELECT<\/span> \n    productLine, \n    SUM<\/span>(orderValue) totalOrderValue\nFROM<\/span>\n    sales\nGROUP<\/span> BY<\/span> \n    productline WITH<\/span> ROLLUP<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here is the output:<\/p>\n\n\n
    +------------------+-----------------+\r\n| productLine      | totalOrderValue |\r\n+------------------+-----------------+\r\n| Classic Cars     |      3853922.49<\/span> |\r\n| Motorcycles      |      1121426.12<\/span> |\r\n| Planes           |       954637.54<\/span> |\r\n| Ships            |       663998.34<\/span> |\r\n| Trains           |       188532.92<\/span> |\r\n| Trucks and Buses |      1024113.57<\/span> |\r\n| Vintage Cars     |      1797559.63<\/span> |\r\n| NULL             |      9604190.61<\/span> |\r\n+------------------+-----------------+\r\n8<\/span> rows in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    As clearly shown in the output, the ROLLUP<\/code> clause generates not only the subtotals but also the grand total of the order values.<\/p>\n\n\n\n
    If you have more than one column specified in the GROUP BY<\/code> clause, the ROLLUP<\/code> clause assumes a hierarchy among the input columns.<\/p>\n\n\n\n
    For example:<\/p>\n\n\n
    GROUP BY c1, c2, c3 WITH<\/span> ROLLUP<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The ROLLUP<\/code> assumes that there is the following hierarchy:<\/p>\n\n\n
    c1 > c2 > c3<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It generates the following grouping sets:<\/p>\n\n\n
    (c1, c2, c3)\n(c1, c2)\n(c1)\n()<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    If you have two columns specified in the GROUP BY<\/code> clause:<\/p>\n\n\n
    GROUP BY c1, c2 WITH<\/span> ROLLUP<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    then the ROLLUP<\/code> generates the following grouping sets:<\/p>\n\n\n
    (c1, c2)\n(c1)\n()<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    See the following query example:<\/p>\n\n\n
    SELECT<\/span> \n    productLine, \n    orderYear,\n    SUM<\/span>(orderValue) totalOrderValue\nFROM<\/span>\n    sales\nGROUP<\/span> BY<\/span> \n    productline, \n    orderYear \nWITH<\/span> ROLLUP<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here is the output:<\/p>\n\n\n
    +------------------+-----------+-----------------+\r\n| productLine      | orderYear | totalOrderValue |\r\n+------------------+-----------+-----------------+\r\n| Classic Cars     |      2003<\/span> |      1374832.22<\/span> |\r\n| Classic Cars     |      2004<\/span> |      1763136.73<\/span> |\r\n| Classic Cars     |      2005<\/span> |       715953.54<\/span> |\r\n| Classic Cars     |      NULL<\/span> |      3853922.49<\/span> |\r\n| Motorcycles      |      2003<\/span> |       348909.24<\/span> |\r\n| Motorcycles      |      2004<\/span> |       527243.84<\/span> |\r\n| Motorcycles      |      2005<\/span> |       245273.04<\/span> |\r\n| Motorcycles      |      NULL<\/span> |      1121426.12<\/span> |\r\n| Planes           |      2003<\/span> |       309784.20<\/span> |\r\n| Planes           |      2004<\/span> |       471971.46<\/span> |\r\n| Planes           |      2005<\/span> |       172881.88<\/span> |\r\n| Planes           |      NULL<\/span> |       954637.54<\/span> |\r\n| Ships            |      2003<\/span> |       222182.08<\/span> |\r\n| Ships            |      2004<\/span> |       337326.10<\/span> |\r\n| Ships            |      2005<\/span> |       104490.16<\/span> |\r\n| Ships            |      NULL<\/span> |       663998.34<\/span> |\r\n| Trains           |      2003<\/span> |        65822.05<\/span> |\r\n| Trains           |      2004<\/span> |        96285.53<\/span> |\r\n| Trains           |      2005<\/span> |        26425.34<\/span> |\r\n| Trains           |      NULL<\/span> |       188532.92<\/span> |\r\n| Trucks and<\/span> Buses |      2003<\/span> |       376657.12<\/span> |\r\n| Trucks and<\/span> Buses |      2004<\/span> |       465390.00<\/span> |\r\n| Trucks and<\/span> Buses |      2005<\/span> |       182066.45<\/span> |\r\n| Trucks and<\/span> Buses |      NULL<\/span> |      1024113.57<\/span> |\r\n| Vintage Cars     |      2003<\/span> |       619161.48<\/span> |\r\n| Vintage Cars     |      2004<\/span> |       854551.85<\/span> |\r\n| Vintage Cars     |      2005<\/span> |       323846.30<\/span> |\r\n| Vintage Cars     |      NULL<\/span> |      1797559.63<\/span> |\r\n| NULL<\/span>             |      NULL<\/span> |      9604190.61<\/span> |\r\n+------------------+-----------+-----------------+\r\n29<\/span> rows in set (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    The ROLLUP<\/code> generates the subtotal row every time the product line changes and the grand total at the end of the result.<\/p>\n\n\n\n
    The hierarchy in this case is:<\/p>\n\n\n
    productLine > orderYear<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    If you reverse the hierarchy, for example:<\/p>\n\n\n
    SELECT<\/span> \n    orderYear,\n    productLine, \n    SUM<\/span>(orderValue) totalOrderValue\nFROM<\/span>\n    sales\nGROUP<\/span> BY<\/span> \n    orderYear,\n    productline\nWITH<\/span> ROLLUP<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +-----------+------------------+-----------------+\r\n| orderYear | productLine      | totalOrderValue |\r\n+-----------+------------------+-----------------+\r\n|      2003<\/span> | Classic Cars     |      1374832.22<\/span> |\r\n|      2003<\/span> | Motorcycles      |       348909.24<\/span> |\r\n|      2003<\/span> | Planes           |       309784.20<\/span> |\r\n|      2003<\/span> | Ships            |       222182.08<\/span> |\r\n|      2003<\/span> | Trains           |        65822.05<\/span> |\r\n|      2003<\/span> | Trucks and<\/span> Buses |       376657.12<\/span> |\r\n|      2003<\/span> | Vintage Cars     |       619161.48<\/span> |\r\n|      2003<\/span> | NULL<\/span>             |      3317348.39<\/span> |\r\n|      2004<\/span> | Classic Cars     |      1763136.73<\/span> |\r\n|      2004<\/span> | Motorcycles      |       527243.84<\/span> |\r\n|      2004<\/span> | Planes           |       471971.46<\/span> |\r\n|      2004<\/span> | Ships            |       337326.10<\/span> |\r\n|      2004<\/span> | Trains           |        96285.53<\/span> |\r\n|      2004<\/span> | Trucks and<\/span> Buses |       465390.00<\/span> |\r\n|      2004<\/span> | Vintage Cars     |       854551.85<\/span> |\r\n|      2004<\/span> | NULL<\/span>             |      4515905.51<\/span> |\r\n|      2005<\/span> | Classic Cars     |       715953.54<\/span> |\r\n|      2005<\/span> | Motorcycles      |       245273.04<\/span> |\r\n|      2005<\/span> | Planes           |       172881.88<\/span> |\r\n|      2005<\/span> | Ships            |       104490.16<\/span> |\r\n|      2005<\/span> | Trains           |        26425.34<\/span> |\r\n|      2005<\/span> | Trucks and<\/span> Buses |       182066.45<\/span> |\r\n|      2005<\/span> | Vintage Cars     |       323846.30<\/span> |\r\n|      2005<\/span> | NULL<\/span>             |      1770936.71<\/span> |\r\n|      NULL<\/span> | NULL<\/span>             |      9604190.61<\/span> |\r\n+-----------+------------------+-----------------+\r\n25<\/span> rows in set (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    The ROLLUP<\/code> generates the subtotal every time the year changes and the grand total at the end of the result set.<\/p>\n\n\n\n
    The hierarchy in this example is:<\/p>\n\n\n
    orderYear > productLine\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The GROUPING() function<\/h2>\n\n\n\n
    To check whether NULL<\/code> in the result set represents the subtotals or grand totals, you use the GROUPING()<\/code> function.<\/p>\n\n\n\n
    The GROUPING()<\/code> function returns 1 when NULL<\/code> occurs in a supper-aggregate row, otherwise, it returns 0.<\/p>\n\n\n\n
    The GROUPING()<\/code> function can be used in the select list, HAVING<\/code> clause, and (as of MySQL 8.0.12 ) ORDER BY<\/code> clause.<\/p>\n\n\n\n
    Consider the following query:<\/p>\n\n\n
    SELECT<\/span> \n    orderYear,\n    productLine, \n    SUM<\/span>(orderValue) totalOrderValue,\n    GROUPING<\/span>(orderYear),\n    GROUPING<\/span>(productLine)\nFROM<\/span>\n    sales\nGROUP<\/span> BY<\/span> \n    orderYear,\n    productline\nWITH<\/span> ROLLUP<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +-----------+------------------+-----------------+---------------------+-----------------------+\r\n| orderYear | productLine      | totalOrderValue | GROUPING(orderYear) | GROUPING(productLine) |\r\n+-----------+------------------+-----------------+---------------------+-----------------------+\r\n|      2003<\/span> | Classic Cars     |      1374832.22<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2003<\/span> | Motorcycles      |       348909.24<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2003<\/span> | Planes           |       309784.20<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2003<\/span> | Ships            |       222182.08<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2003<\/span> | Trains           |        65822.05<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2003<\/span> | Trucks and<\/span> Buses |       376657.12<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2003<\/span> | Vintage Cars     |       619161.48<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2003<\/span> | NULL<\/span>             |      3317348.39<\/span> |                   0<\/span> |                     1<\/span> |\r\n|      2004<\/span> | Classic Cars     |      1763136.73<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2004<\/span> | Motorcycles      |       527243.84<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2004<\/span> | Planes           |       471971.46<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2004<\/span> | Ships            |       337326.10<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2004<\/span> | Trains           |        96285.53<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2004<\/span> | Trucks and<\/span> Buses |       465390.00<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2004<\/span> | Vintage Cars     |       854551.85<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2004<\/span> | NULL<\/span>             |      4515905.51<\/span> |                   0<\/span> |                     1<\/span> |\r\n|      2005<\/span> | Classic Cars     |       715953.54<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2005<\/span> | Motorcycles      |       245273.04<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2005<\/span> | Planes           |       172881.88<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2005<\/span> | Ships            |       104490.16<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2005<\/span> | Trains           |        26425.34<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2005<\/span> | Trucks and<\/span> Buses |       182066.45<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2005<\/span> | Vintage Cars     |       323846.30<\/span> |                   0<\/span> |                     0<\/span> |\r\n|      2005<\/span> | NULL<\/span>             |      1770936.71<\/span> |                   0<\/span> |                     1<\/span> |\r\n|      NULL<\/span> | NULL<\/span>             |      9604190.61<\/span> |                   1<\/span> |                     1<\/span> |\r\n+-----------+------------------+-----------------+---------------------+-----------------------+\r\n25<\/span> rows in set (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    The GROUPING(orderYear)<\/code> returns 1 when NULL<\/code> in the orderYear<\/code> column occurs in a super-aggregate row, 0 otherwise.<\/p>\n\n\n\n
    Similarly, the GROUPING(productLine)<\/code> returns 1 when NULL<\/code> in the productLine<\/code> column occurs in a super-aggregate row, 0 otherwise.<\/p>\n\n\n\n
    We often use GROUPING()<\/code> function to substitute meaningful labels for super-aggregate NULL<\/code> values instead of displaying it directly.<\/p>\n\n\n\n
    The following example shows how to combine the IF()<\/a><\/code> function with the GROUPING()<\/code> function to substitute labels for the super-aggregate NULL<\/code> values in orderYear<\/code> and productLine<\/code> columns:<\/p>\n\n\n
    SELECT<\/span> \n    IF<\/span>(GROUPING<\/span>(orderYear),\n        'All Years'<\/span>,\n        orderYear) orderYear,\n    IF<\/span>(GROUPING<\/span>(productLine),\n        'All Product Lines'<\/span>,\n        productLine) productLine,\n    SUM<\/span>(orderValue) totalOrderValue\nFROM<\/span>\n    sales\nGROUP<\/span> BY<\/span> \n    orderYear , \n    productline \nWITH<\/span> ROLLUP<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The output is:<\/p>\n\n\n
    +-----------+-------------------+-----------------+\r\n| orderYear | productLine       | totalOrderValue |\r\n+-----------+-------------------+-----------------+\r\n| 2003      | Classic Cars      |      1374832.22 |\r\n| 2003      | Motorcycles       |       348909.24 |\r\n| 2003      | Planes            |       309784.20 |\r\n| 2003      | Ships             |       222182.08 |\r\n| 2003      | Trains            |        65822.05 |\r\n| 2003      | Trucks and Buses  |       376657.12 |\r\n| 2003      | Vintage Cars      |       619161.48 |\r\n| 2003      | All Product Lines |      3317348.39 |\r\n| 2004      | Classic Cars      |      1763136.73 |\r\n| 2004      | Motorcycles       |       527243.84 |\r\n| 2004      | Planes            |       471971.46 |\r\n| 2004      | Ships             |       337326.10 |\r\n| 2004      | Trains            |        96285.53 |\r\n| 2004      | Trucks and Buses  |       465390.00 |\r\n| 2004      | Vintage Cars      |       854551.85 |\r\n| 2004      | All Product Lines |      4515905.51 |\r\n| 2005      | Classic Cars      |       715953.54 |\r\n| 2005      | Motorcycles       |       245273.04 |\r\n| 2005      | Planes            |       172881.88 |\r\n| 2005      | Ships             |       104490.16 |\r\n| 2005      | Trains            |        26425.34 |\r\n| 2005      | Trucks and Buses  |       182066.45 |\r\n| 2005      | Vintage Cars      |       323846.30 |\r\n| 2005      | All Product Lines |      1770936.71 |\r\n| All Years | All Product Lines |      9604190.61 |\r\n+-----------+-------------------+-----------------+<\/code><\/span><\/pre>\n\n\n
    In this tutorial, you have learned how to use the MySQL ROLLUP()<\/code> to generate multiple grouping sets considering a hierarchy between columns specified in the GROUP BY<\/code> clause.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
If you want to generate two or more grouping sets together in one query, you may use the UNION ALL<\/a><\/code> operator as follows:<\/p>\n\n\n