You can use the GROUP BY
clause to group the query result. You can use the GROUPING SETS
, CUBE
, and ROLLUP
options in the GROUP BY
clause to show the grouping result in different forms.
GROUP BY expression [, ...]
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.
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), ())
Precautions
- You must use standard aggregate functions such as
SUM
,AVG
, andCOUNT
to specify the columns that are not used for grouping. Otherwise, theGROUP BY
clause does not take effect. - The
GROUP BY
clause must contain all the columns and non-aggregate expressions specified for the SELECT statement.
Example
The following statement contains two aggregate expressions. The first aggregate expression
uses the SUM
function, and the second uses the COUNT
function. The LISTID
and EVENTID
columns are used for grouping.
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)
You can also use sequence numbers to reference columns in the GROUP BY
clause.
For example, you can modify the preceding statement as follows:
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