Product DocumentationAppearance
GROUP BY ROLLUP
Return aggregated results over the specified list of grouping sets and rolled up sets from that list.
GROUP BY ROLLUP (expression [, ...])Like GROUP BY CUBE, GROUP BY ROLLUP is a convenient shorthand for a more detailed GROUPING SETS construct.
For example, the following clause:
ROLLUP(expr1, expr2, expr3)is evaluated in the same way as:
GROUPING SETS(
(expr1, expr2, expr3),
(expr1, expr2),
(expr1),
()
)GROUP BY ROLLUP is useful for analyzing data that has a hierarchical structure, such as total population by city, county, state, and country, or goals scored by match, competition, season, and club career.
Duplicate grouping sets are not allowed.
Examples
The examples in this section are based on the following set of rows in the player table:
premdb=# select * from player;
playerid | teamid | seasonid | firstname | lastname | position | dob | weekly_wages | avg_mins_per_match | matches_played | cob
----------+--------+----------+-----------+-----------+----------+------------+--------------+--------------------+----------------+-------------
1 | 2 | 27 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
2 | 41 | 27 | Harry | Kane | F | 1993-07-28 | 250000 | 84.1567 | 36.1 | England
3 | 41 | 27 | Harry | Winks | M | 1996-02-02 | 40000 | 72.3412 | 31.2 | England
4 | 24 | 27 | Kevin | De Bruyne | M | 1991-06-28 | 418000 | 86.0981 | 35.3 | Netherlands
5 | 25 | 27 | Paul | Pogba | M | 1993-03-15 | 378000 | 76.5341 | 29.4 | France
6 | 25 | 27 | Alexis | Sanchez | F | 1988-12-19 | 410000 | 71.2309 | 31.5 | Chile
7 | 13 | 27 | Gonzalo | Higuain | F | 1987-12-10 | 352000 | 61.9803 | 26.6 | Argentina
8 | 1 | 27 | Mesut | Ozil | M | 1988-10-15 | 350000 | 64.4561 | 32.7 | Germany
9 | 13 | 27 | Eden | Hazard | M | 1991-01-07 | 200000 | 81.2387 | 36.8 | Netherlands
10 | 24 | 27 | Sergio | Aguero | F | 1988-06-02 | 220000 | 80.0032 | 33.9 | Argentina
11 | 25 | 27 | Romelu | Lukaku | M | 1993-05-13 | 250000 | 85.1897 | 34 | Belgium
12 | 25 | 27 | David | de Gea | G | 1990-11-07 | 200000 | 88.5764 | 35.1 | Spain
13 | 23 | 27 | Mo | Salah | F | 1992-06-15 | 200000 | 82.2765 | 36.2 | Egypt
14 | 24 | 27 | Riyad | Mahrez | M | 1991-02-21 | 200000 | 55.1908 | 24.3 | Algeria
15 | 23 | 27 | Virgil | Van Dijk | D | 1991-07-08 | 180000 | 87.6345 | 37.4 | Netherlands
16 | 24 | 27 | David | Silva | M | 1986-01-08 | 250000 | 79.6723 | 30.5 | Spain
17 | 22 | 27 | Jamie | Vardy | F | 1987-01-11 | 80000 | 77.1986 | 31.6 | England
18 | 41 | 27 | Dele | Alli | M | 1996-04-11 | 100000 | 75.9013 | 30.7 | England
19 | 41 | 27 | Marcus | Rashford | F | 1997-10-31 | 250000 | 79.9912 | 32.8 | England
20 | 47 | 27 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL
(20 rows)Note that this table contains stored NULL values in two of its rows.
Use GROUP BY ROLLUP over the position and cob columns, with a SUM on weekly_wages:
premdb=# select position, cob, sum(weekly_wages)
from player
group by rollup(position, cob)
order by 1, 2, 3;
position | cob | sum
----------+-------------+---------
D | Netherlands | 180000
D | NULL | 180000
F | Argentina | 572000
F | Chile | 410000
F | Egypt | 200000
F | England | 580000
F | NULL | 1762000
G | Spain | 200000
G | NULL | 200000
M | Algeria | 200000
M | Belgium | 250000
M | England | 140000
M | France | 378000
M | Germany | 350000
M | Netherlands | 618000
M | Spain | 250000
M | NULL | 2186000
NULL | NULL | 4328000
NULL | NULL | NULL
NULL | NULL | NULL
(20 rows)This example rolls up over three different columns:
premdb=# select position, weekly_wages wages, cob country, sum(matches_played) matches
from player
group by rollup(position, wages, country)
order by 1, 2, 3;
position | wages | country | matches
----------+--------+-------------+---------
D | 180000 | Netherlands | 37.4
D | 180000 | [NULL] | 37.4
D | [NULL] | [NULL] | 37.4
F | 80000 | England | 31.6
F | 80000 | [NULL] | 31.6
F | 200000 | Egypt | 36.2
F | 200000 | [NULL] | 36.2
F | 220000 | Argentina | 33.9
F | 220000 | [NULL] | 33.9
F | 250000 | England | 68.9
F | 250000 | [NULL] | 68.9
F | 352000 | Argentina | 26.6
F | 352000 | [NULL] | 26.6
F | 410000 | Chile | 31.5
F | 410000 | [NULL] | 31.5
F | [NULL] | [NULL] | 228.7
G | 200000 | Spain | 35.1
G | 200000 | [NULL] | 35.1
G | [NULL] | [NULL] | 35.1
M | 40000 | England | 31.2
M | 40000 | [NULL] | 31.2
M | 100000 | England | 30.7
M | 100000 | [NULL] | 30.7
M | 200000 | Algeria | 24.3
M | 200000 | Netherlands | 36.8
M | 200000 | [NULL] | 61.1
M | 250000 | Belgium | 34
M | 250000 | Spain | 30.5
M | 250000 | [NULL] | 64.5
M | 350000 | Germany | 32.7
M | 350000 | [NULL] | 32.7
M | 378000 | France | 29.4
M | 378000 | [NULL] | 29.4
M | 418000 | Netherlands | 35.3
M | 418000 | [NULL] | 35.3
M | [NULL] | [NULL] | 284.9
[NULL] | [NULL] | [NULL] | [NULL]
[NULL] | [NULL] | [NULL] | 586.1
[NULL] | [NULL] | [NULL] | [NULL]
[NULL] | [NULL] | [NULL] | [NULL]
(40 rows)A similar example rolls up by team, position, and country, and produces the average weekly wage (cast as an integer) for each set:
premdb=# select teamid team, position, cob country, avg(weekly_wages)::int matches
from player
group by rollup(team, position, country)
order by 1, 2, 3, 4;
team | position | country | matches
--------+----------+-------------+---------
1 | M | Germany | 350000
1 | M | [NULL] | 350000
1 | [NULL] | [NULL] | 350000
2 | [NULL] | [NULL] | [NULL]
2 | [NULL] | [NULL] | [NULL]
2 | [NULL] | [NULL] | [NULL]
13 | F | Argentina | 352000
13 | F | [NULL] | 352000
13 | M | Netherlands | 200000
13 | M | [NULL] | 200000
13 | [NULL] | [NULL] | 276000
22 | F | England | 80000
22 | F | [NULL] | 80000
22 | [NULL] | [NULL] | 80000
23 | D | Netherlands | 180000
23 | D | [NULL] | 180000
23 | F | Egypt | 200000
23 | F | [NULL] | 200000
23 | [NULL] | [NULL] | 190000
24 | F | Argentina | 220000
24 | F | [NULL] | 220000
24 | M | Algeria | 200000
24 | M | Netherlands | 418000
24 | M | Spain | 250000
24 | M | [NULL] | 289333
24 | [NULL] | [NULL] | 272000
25 | F | Chile | 410000
25 | F | [NULL] | 410000
25 | G | Spain | 200000
25 | G | [NULL] | 200000
25 | M | Belgium | 250000
25 | M | France | 378000
25 | M | [NULL] | 314000
25 | [NULL] | [NULL] | 309500
41 | F | England | 250000
41 | F | [NULL] | 250000
41 | M | England | 70000
41 | M | [NULL] | 70000
41 | [NULL] | [NULL] | 160000
47 | [NULL] | [NULL] | [NULL]
47 | [NULL] | [NULL] | [NULL]
47 | [NULL] | [NULL] | [NULL]
[NULL] | [NULL] | [NULL] | 240444
(43 rows)Parent topic:GROUP BY Clause