GROUP BY

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, and COUNT to specify the columns that are not used for grouping. Otherwise, the GROUP 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