All Products
Search

This Product

    AnalyticDB:GROUP BY

    Document Center

    AnalyticDB:GROUP BY

    Last Updated:Mar 14, 2025

    You can use the GROUP BY clause to group the aggregate results based on the specified columns. You can also use the GROUPING SETS, WITH CUBE, or ROLLUP option in the GROUP BY clause to show the grouping result in different forms. 

    GROUP BY expression [, ...]

    Usage notes

    • You must use standard aggregate functions such as SUM(), AVG(), or COUNT() to declare non-GROUP BY columns. Otherwise, the ARBITRARY() function is used to return a non-GROUP BY column in a random manner.

    • The ONLY_FULL_GROUP_BY mode is not supported in AnalyticDB for MySQL.

    GROUPING SETS

    You can use the GROUPING SETS option to perform aggregation on the same dataset across multiple dimensions and combine the results into a single result set. The GROUPING SETS option allows you to specify multiple GROUP BY conditions for one query, which is equivalent to the union of multiple GROUP BY clauses. 

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

    The preceding statement is equivalent to the following statement:

    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;

    CUBE

    The CUBE option is similar to the GROUPING SETS option, but the CUBE option automatically generates all possible grouping sets without the need to manually specify the groups. 

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

    The preceding statement is equivalent to the following statement:

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

    ROLLUP

    The ROLLUP option generates hierarchical grouping sets for each GROUP BY column. The grouping dimensions are progressively reduced in the specified order of columns to produce an overall summary. 

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

    The preceding statement is equivalent to the following statement:

    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), 
    ()
    );

    Usage notes

    AnalyticDB for MySQL does not allow GROUP BY ROLLUP (...) to be followed by column names. For example, an error occurs when the following statement is executed: 

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

    If you want to implement the union of GROUP BY clauses in a hierarchical manner, you can specify the GROUP BY GROUPING SETS ((origin_state, origin_zip), (origin_state), ()), destination_state clause.

    Examples

    The following examples show the effects of using the GROUP BY clause, the GROUP SETS, WITH CUBE and ROLLUP options. In these examples, the sales table is used.

    Execute the following statements to create a table named sales and insert data into the sales table: 

    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: Query data by using the GROUP BY clause.

    Execute the following statement to query the total values of the amount column that grouped by the year and region columns: 

    SELECT year, region, SUM(amount) as sum_amount
FROM sales
GROUP BY year, region
ORDER BY 3,2, 1 -- Sort the results in this order: the sum_amount column, the region column, and the year column. 
LIMIT 5;

    Sample 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)

    You can also use sequence numbers to reference columns in the GROUP BY clause. You can change the preceding statement to the following statement: 

    SELECT year, region, SUM(amount) as sum_amount
FROM sales
GROUP BY 1,2 -- Group the results by the year and region columns.
ORDER BY 3, 2, 1
LIMIT 5;

    Sample 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)

    Example 2: Execute the following statement to combine the grouping results by the year and region columns, grouping results by the year column, grouping results by the region column, and the overall summary results into a single result set by using the GROUPING SETS option: 

    SELECT year, region, SUM(amount) as sum_amount
FROM sales
GROUP BY GROUPING SETS (
    (year,region), -- Group the results by the year and region columns. 
    (region), -- Group the results by the region column. 
    (year), -- Group the results by the year column.
    () -- Produce the overall summary results.
    );

    Sample 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)

    Example 3: Execute the following statement by using the GROUP BY ... WITH CUBE clause to generate all possible grouping sets. This produces the same effect as Example 2 with the GROUPING SETS option. 

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

    Sample 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)

    Example 4: Execute the following statement by using the GROUP BY ROLLUP(...) clause to generate the most detailed summary results (aggregating by the year and region columns), then gradually reduce the grouping dimensions (aggregating by the year column), and finally produce the overall summary results. 

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

    Sample 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)