GROUP BY syntax and examples-AnalyticDB(AnalyticDB)-阿里云帮助中心

GROUP BY groups query results by one or more columns and aggregates values within each group. AnalyticDB for MySQL also supports three extensions — GROUPING SETS, CUBE, and ROLLUP — for multi-dimensional aggregation in a single query.

GROUP BY expression [, ...]

Usage notes

Non-GROUP BY columns must use a standard aggregate function such as SUM(), AVG(), or COUNT(). Without one, AnalyticDB for MySQL applies ARBITRARY(), which returns a random value from the group.
ONLY_FULL_GROUP_BY mode is not supported in AnalyticDB for MySQL.

GROUPING SETS

GROUPING SETS runs aggregations across multiple grouping dimensions in a single query and combines the results into one result set. It is equivalent to a UNION ALL of separate GROUP BY queries, one per grouping set.

SELECT origin_state, origin_zip, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state),
    (origin_state, origin_zip),
    (destination_state));

This is equivalent to:

SELECT origin_state, NULL, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state
UNION ALL
SELECT origin_state, origin_zip, NULL, sum(package_weight)
FROM shipping GROUP BY origin_state, origin_zip
UNION ALL
SELECT NULL, NULL, destination_state, sum(package_weight)
FROM shipping GROUP BY destination_state;

NULL values in results: Columns not part of a given grouping set appear as NULL in that row.

CUBE

CUBE automatically generates all possible grouping sets from the listed columns, so you do not need to enumerate them manually.

SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY origin_state, destination_state WITH CUBE;

This is equivalent to:

SELECT origin_state, destination_state, sum(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state, destination_state),
    (origin_state),
    (destination_state),
    ());

The empty grouping set () produces the grand total across all rows.

ROLLUP

ROLLUP generates a hierarchy of grouping sets by progressively dropping columns from right to left, then adds a grand total. Use it when you need subtotals at each level of a hierarchy without writing separate queries — for example, totals per year-and-region, then per year only, then an overall grand total.

List the columns from most significant to least significant. The order determines the rollup hierarchy: the first column is the top level, and each subsequent column adds a finer level of detail.

SELECT origin_state, origin_zip, destination_state, SUM(package_weight)
FROM shipping
GROUP BY ROLLUP (origin_state, origin_zip, destination_state);

This is equivalent to:

SELECT origin_state, origin_zip, destination_state, SUM(package_weight)
FROM shipping
GROUP BY GROUPING SETS (
    (origin_state, origin_zip, destination_state),
    (origin_state, origin_zip),
    (origin_state),
    ());

Limitation: In AnalyticDB for MySQL, GROUP BY ROLLUP (...) cannot be followed by additional column names. The following statement causes an error:

SELECT origin_state, origin_zip, destination_state, SUM(package_weight)
FROM shipping
GROUP BY ROLLUP (origin_state, origin_zip), destination_state;

To achieve the equivalent result, use GROUPING SETS directly:

GROUP BY GROUPING SETS ((origin_state, origin_zip), (origin_state), ()), destination_state

Examples

All examples use the sales table. Create the table and insert sample data:

CREATE TABLE sales (
    year INT,
    region VARCHAR(50),
    amount DECIMAL(10, 2)
);

INSERT INTO sales (year, region, amount) VALUES
(2020, 'North', 1000.00),
(2020, 'South', 1500.00),
(2020, 'East', 1200.00),
(2020, 'West', 1300.00),
(2021, 'North', 2000.00),
(2021, 'South', 2500.00),
(2021, 'East', 2200.00),
(2021, 'West', 2300.00),
(2022, 'North', 3000.00),
(2022, 'South', 3500.00),
(2022, 'East', 3200.00),
(2022, 'West', 3300.00);

Example 1: GROUP BY with ORDER BY and LIMIT

Group sales by year and region, sort by total amount, and return the top 5 rows:

SELECT year, region, SUM(amount) AS sum_amount
FROM sales
GROUP BY year, region
ORDER BY 3, 2, 1  -- Sort by sum_amount, then region, then year
LIMIT 5;

Result:

 year | region | sum_amount
------+--------+------------
 2020 | North  |    1000.00
 2020 | East   |    1200.00
 2020 | West   |    1300.00
 2020 | South  |    1500.00
 2021 | North  |    2000.00
(5 rows)

Column positions can be used in GROUP BY as a shorthand for column names. The following statement produces the same result:

SELECT year, region, SUM(amount) AS sum_amount
FROM sales
GROUP BY 1, 2  -- Group by year, then region
ORDER BY 3, 2, 1
LIMIT 5;

Example 2: GROUPING SETS

Combine groupings by (year, region), by region alone, by year alone, and the grand total — all in one query:

SELECT year, region, SUM(amount) AS sum_amount
FROM sales
GROUP BY GROUPING SETS (
    (year, region),  -- Group by year and region
    (region),        -- Group by region
    (year),          -- Group by year
    ()               -- Grand total
);

Result:

 year | region | sum_amount
------+--------+------------
 2020 | North  |    1000.00
 2020 | East   |    1200.00
 2020 | West   |    1300.00
 2020 | South  |    1500.00
 2021 | North  |    2000.00
 2021 | East   |    2200.00
 2021 | West   |    2300.00
 2021 | South  |    2500.00
 2022 | North  |    3000.00
 2022 | East   |    3200.00
 2022 | West   |    3300.00
 2022 | South  |    3500.00
 NULL | North  |    6000.00
 NULL | East   |    6600.00
 NULL | West   |    6900.00
 NULL | South  |    7500.00
 2020 | NULL   |    5000.00
 2021 | NULL   |    9000.00
 2022 | NULL   |   13000.00
 NULL | NULL   |   27000.00
(20 rows)

Rows where year is NULL are region subtotals. Rows where region is NULL are year subtotals. The row where both are NULL is the grand total (27000.00).

Example 3: CUBE

GROUP BY ... WITH CUBE automatically generates all grouping combinations. This produces the same 20-row result as Example 2:

SELECT year, region, SUM(amount) AS sum_amount
FROM sales
GROUP BY year, region WITH CUBE;

Example 4: ROLLUP

GROUP BY ROLLUP(...) produces a hierarchy: detailed rows first (by year and region), then year subtotals, then the grand total. Unlike CUBE, it does not generate all combinations — only the hierarchical rollup from most detailed to least detailed.

SELECT year, region, SUM(amount) AS sum_amount
FROM sales
GROUP BY ROLLUP(year, region);

Result:

 year | region | sum_amount
------+--------+------------
 2020 | North  |    1000.00
 2020 | East   |    1200.00
 2020 | West   |    1300.00
 2020 | South  |    1500.00
 2021 | North  |    2000.00
 2021 | East   |    2200.00
 2021 | West   |    2300.00
 2021 | South  |    2500.00
 2022 | North  |    3000.00
 2022 | East   |    3200.00
 2022 | West   |    3300.00
 2022 | South  |    3500.00
 2020 | NULL   |    5000.00
 2021 | NULL   |    9000.00
 2022 | NULL   |   13000.00
 NULL | NULL   |   27000.00
(16 rows)

Compared to Example 2 (GROUPING SETS), ROLLUP omits the region-only subtotals (NULL | North, NULL | East, etc.), because rolling up (year, region) only aggregates by year at the next level, not by region independently.