GROUP BY syntax, usage notes, and examples - AnalyticDB - Alibaba Cloud Documentation Center

You can use the GROUP BY clause to group query results based on the specified columns. You can also use the GROUPING SETS, CUBE, and ROLLUP options in the GROUP BY clause to show the grouping results in different forms.

GROUP BY expression [, ...]

Usage notes

You must use standard aggregate functions such as SUM, AVG, and COUNT to declare the columns other than the GROUP BY columns. Otherwise, the ARBITRARY function is used.
The GROUP BY clause must contain all the columns and non-aggregate expressions that are specified for the SELECT statement.

GROUPING SETS

The GROUPING SETS option is used 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 used to list all possible grouping sets.

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 is used to list the grouping sets in a hierarchical manner.

Important

AnalyticDB for MySQL does not allow GROUP BY ROLLUP () followed by column names. If you want to implement the union of GROUP BY clauses in a hierarchical manner, you can use GROUP BY GROUPING SETS ((column1, column2), (column1), ()), column3;.

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

The preceding statement is equivalent to the following statement:

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

Examples

The following statement contains two aggregate expressions. The first expression uses the SUM function, and the second expression uses the COUNT function. listid and eventid are GROUP BY columns.

SELECT listid, eventid, SUM(pricepaid) as revenue,
count(qtysold) as numtix
FROM sales
GROUP BY listid, eventid
ORDER BY 3, 4, 2, 1
LIMIT 5;

Sample result:

listid | eventid | revenue | numtix
-------+---------+---------+--------
89397  |      47 |   20.00 |      1
106590 |      76 |   20.00 |      1
124683 |     393 |   20.00 |      1
103037 |     403 |   20.00 |      1
147685 |     429 |   20.00 |      1
(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 listid, eventid, SUM(pricepaid) as revenue,
count(qtysold) as numtix
FROM sales
GROUP BY 1,2
ORDER BY 3, 4, 2, 1
LIMIT 5;

Sample result:

listid | eventid | revenue | numtix
-------+---------+---------+--------
89397  |      47 |   20.00 |      1
106590 |      76 |   20.00 |      1
124683 |     393 |   20.00 |      1
103037 |     403 |   20.00 |      1
147685 |     429 |   20.00 |      1