HeatWave Release Notes
As of MySQL 8.3.0, and only available in the HeatWave
secondary engine, the GROUP BY clause
permits a CUBE modifier in addition to
the ROLLUP modifier. See:
GROUP BY Modifiers for the
ROLLUP modifier.
The ROLLUP modifier generates aggregated
results that follow the hierarchy for the selected columns.
The CUBE modifier generates aggregated
results for all possible combinations of the selected
columns. For a single column the results are the same.
A ROLLUP modifier example:
mysql> SELECT
IF(GROUPING(year), 'All years', year) AS year,
IF(GROUPING(country), 'All countries', country) AS country,
IF(GROUPING(product), 'All products', product) AS product,
SUM(profit) AS profit
FROM sales
GROUP BY ROLLUP (year, country, product);
+-----------+---------------+--------------+--------+
| year | country | product | profit |
+-----------+---------------+--------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | All products | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | All products | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | All products | 1575 |
| 2000 | All countries | All products | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | All products | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | All products | 3000 |
| 2001 | All countries | All products | 3010 |
| All years | All countries | All products | 7535 |
+-----------+---------------+--------------+--------+
A CUBE modifier example that uses the
same data:
mysql> SELECT
IF(GROUPING(year), 'All years', year) AS year,
IF(GROUPING(country), 'All countries', country) AS country,
IF(GROUPING(product), 'All products', product) AS product,
SUM(profit) AS profit
FROM sales
GROUP BY CUBE (year, country, product);
+-----------+---------------+--------------+--------+
| year | country | product | profit |
+-----------+---------------+--------------+--------+
| 2001 | USA | Computer | 2700 |
| 2000 | USA | Computer | 1500 |
| 2000 | India | Calculator | 150 |
| 2001 | USA | TV | 250 |
| 2000 | USA | Calculator | 75 |
| 2000 | Finland | Phone | 100 |
| 2001 | Finland | Phone | 10 |
| 2000 | Finland | Computer | 1500 |
| 2001 | USA | Calculator | 50 |
| 2000 | India | Computer | 1200 |
| All years | All countries | All products | 7535 |
| 2001 | All countries | All products | 3010 |
| 2000 | All countries | All products | 4525 |
| All years | India | All products | 1350 |
| All years | Finland | All products | 1610 |
| All years | USA | All products | 4575 |
| 2001 | USA | All products | 3000 |
| 2000 | India | All products | 1350 |
| 2000 | Finland | All products | 1600 |
| 2000 | USA | All products | 1575 |
| 2001 | Finland | All products | 10 |
| All years | All countries | TV | 250 |
| All years | All countries | Computer | 6900 |
| All years | All countries | Phone | 110 |
| All years | All countries | Calculator | 275 |
| 2001 | All countries | Computer | 2700 |
| 2000 | All countries | Phone | 100 |
| 2000 | All countries | Calculator | 225 |
| 2001 | All countries | Phone | 10 |
| 2001 | All countries | TV | 250 |
| 2001 | All countries | Calculator | 50 |
| 2000 | All countries | Computer | 4200 |
| All years | Finland | Computer | 1500 |
| All years | USA | Calculator | 125 |
| All years | USA | TV | 250 |
| All years | USA | Computer | 4200 |
| All years | India | Calculator | 150 |
| All years | Finland | Phone | 110 |
| All years | India | Computer | 1200 |
+-----------+---------------+--------------+--------+