`GROUP BY`子句用于对查询结果进行分组，可以在`GROUP BY`中使用`GROUPING SETS``CUBE``ROLLUP`以不同的形式展示分组结果。

``GROUP BY expression [, ...]    ``

## GROUPING SETS

`GROUPING SETS`用于在同一结果集中指定多个`GROUP BY`选项，作用相当于多个`GROUP BY`查询的`UNION`组合形式。

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

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

`CUBE`用于列出所有可能的分组集。

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

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

## ROLLUP

`ROLLUP`可以以层级的方式列出分组集。

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

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

## 注意事项

• 查询中必须使用标准聚合函数（`SUM``AVG``COUNT`）声明非分组列，否则无法使用`GROUP BY`子句。
• `GROUP BY`中的列或表达式列表必须与查询列表中的非聚合表达式的列相同。

## 示例

``````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;
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)         ``````

`GROUP BY`子句中的表达式也可以使用序号来引用所需的列。

``````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;

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