5.3.1.1 GROUP BY Modifiers

In the MySQL HeatWave secondary engine, the GROUP BY clause permits the following modifiers:

A ROLLUP modifier. This is a preferred alternative to the WITH ROLLUP modifier. See ROLLUP and CUBE Modifiers.
A CUBE modifier in addition to the WITH ROLLUP modifier. Available in MySQL HeatWave only. See ROLLUP and CUBE Modifiers.
A GROUPING SETS modifier (as of MySQL 9.6.0). Available in MySQL HeatWave only. See GROUPING SETS Modifier.

ROLLUP and CUBE Modifiers

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 |
+-----------+---------------+--------------+--------+

GROUPING SETS Modifier

Available as of MySQL 9.6.0 only on MySQL HeatWave, the GROUPING SETS modifier allows you to concisely specify multiple groupings of data in a single query. The modifier avoids the need to specify multiple GROUP BY queries combined with UNION ALL.

Use the following syntax:

SELECT 
    ..
FROM 
    table_name
GROUP BY 
    GROUPING SETS <left paren>
        (grouping_set_1),
        (grouping_set_2),
        ..,
        ()
    <right paren>;

Each grouping set is a comma-separated list of GROUP BY columns or expressions. The elements inside each grouping set, and the grouping sets themselves, are comma-separated.

Grouping sets can have one or more grouping sets, up to a maximum of 128 branches.

You can also specify empty grouping sets. Consider the following example:

mysql> SELECT MAX(id)
FROM t1
GROUP BY GROUPING SETS (());

The () represents an empty grouping set, which means the query creates a full-table aggregate.

For example, suppose that a sales table has year, country, product, and profit columns for recording sales profitability:

CREATE TABLE sales(
sales_id INT PRIMARY KEY,
year INT,
country VARCHAR(20),
product VARCHAR(32),
profit INT
);

You can use grouping sets to group the data by country and product, by country only, and by product only. The empty grouping set represents an aggregate of the full table. For example:

mysql> SELECT country, product, SUM(profit) AS total_profit
FROM sales
GROUP BY GROUPING SETS (
  (country, product),
  (country),
  (product),
  ()
);
+---------+---------+--------------+
| country | product | total_profit |
+---------+---------+--------------+
| Canada  | Widget  |          200 |
| USA     | Widget  |          220 |
| Canada  | Gadget  |          180 |
| USA     | Gadget  |          150 |
| USA     | NULL    |          370 |
| Canada  | NULL    |          380 |
| NULL    | Gadget  |          330 |
| NULL    | Widget  |          420 |
| NULL    | NULL    |          750 |
+---------+---------+--------------+

The output creates additional rows with NULL values to provide aggregate values from the grouping sets. The output from the previous example summarizes the following:

The row that has country of USA and product of NULL shows an aggregate total profit value for the USA of 370.
The row that has country of Canada and product of NULL shows an aggregate total profit value for Canada of 380.
The row that has country of NULL and product of Gadget shows an aggregate total profit value for gadgets of 330.
The row that has country of NULL and product of Widget shows an aggregate total profit value for widgets of 420.
The row that has country and product of NULL shows an overall aggregate total profit value of 750.

When using GROUPING SETS, the set of expressions or colulmns within each grouping set must be unique. For example, the following command generates an error because col_1 is duplicated in the first grouping set:

mysql> SELECT COUNT(*)
FROM table_1
GROUP BY GROUPING SETS ((col_1, col_1), (col_1,col_2));

Since the GROUPING_SETS modifier is only supported in MySQL HeatWave, the following query generates an error if the data is not loaded into MySQL HeatWave Cluster:

mysql> SELECT COUNT(*)
FROM table_1
GROUP BY GROUPING SETS ((col_1), (col_2));

However, if the GROUPING_SETS modifier is only an empty grouping, or is a single grouping set, it can run for data not loaded into MySQL HeatWave Cluster. Consider these examples:

mysql> SELECT COUNT(*)
FROM table_1
GROUP BY GROUPING SETS (());

mysql> SELECT COUNT(*)
FROM table_1
GROUP BY GROUPING SETS ((col_1));

mysql> SELECT COUNT(*)
FROM table_1
GROUP BY GROUPING SETS ((col_1, col_2));

What's Next

Learn how to perform the following tasks:
- Run queries
- Modify tables
- Retrieve MySQL HeatWave Metadata
- Optimize workloads for OLAP and OLTP
- Monitor MySQL HeatWave processes