Product DocumentationAppearance
GROUP BY CUBE
Return aggregated values for the given list of grouping sets and all possible subsets of that list.
GROUP BY CUBE (expression [, ...])GROUP BY CUBE is a shorthand form of an exhaustive GROUPING SETS list. For example, the following clause:
CUBE(expr1, expr2, expr3)is evaluated in the same way as:
GROUPING SETS(
(expr1, expr2, expr3),
(expr1, expr2),
(expr1, expr3),
(expr1),
(expr2, expr3),
(expr2),
(expr3),
()
)Note that this list includes the empty set ().
Duplicate grouping sets are not allowed.
GROUP BY CUBE queries are limited to a maximum of 7 columns.
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)This example returns aggregated wages over all combinations of two columns, position and cob:
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] | Algeria | 200000
[NULL] | Argentina | 572000
[NULL] | Belgium | 250000
[NULL] | Chile | 410000
[NULL] | Egypt | 200000
[NULL] | England | 720000
[NULL] | France | 378000
[NULL] | Germany | 350000
[NULL] | Netherlands | 798000
[NULL] | Spain | 450000
[NULL] | [NULL] | 4328000
[NULL] | [NULL] | [NULL]
[NULL] | [NULL] | [NULL]
[NULL] | [NULL] | [NULL]
(31 rows)The following example groups by the cube of three grouping sets, showing how cubed results grow exponentially when multiple expressions are used in the GROUP BY CUBE clause. Note the inclusion of subtotal rows (rows with two NULL values) and a grand total row (the last row, with three NULL values). There are no completely NULL rows in this case because of the WHERE clause constraint.
premdb=# select position, weekly_wages wages, cob country, sum(matches_played) matches
from player
where wages is not null
group by cube(position, wages, country)
order by 1, 2, 3;
position | wages | country | matches
----------+--------+-------------+---------
D | 180000 | Netherlands | 37.4
D | 180000 | [NULL] | 37.4
D | [NULL] | Netherlands | 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] | Argentina | 60.5
F | [NULL] | Chile | 31.5
F | [NULL] | Egypt | 36.2
F | [NULL] | England | 100.5
F | [NULL] | [NULL] | 228.7
G | 200000 | Spain | 35.1
G | 200000 | [NULL] | 35.1
G | [NULL] | Spain | 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] | Algeria | 24.3
M | [NULL] | Belgium | 34
M | [NULL] | England | 61.9
M | [NULL] | France | 29.4
M | [NULL] | Germany | 32.7
M | [NULL] | Netherlands | 72.1
M | [NULL] | Spain | 30.5
M | [NULL] | [NULL] | 284.9
[NULL] | 40000 | England | 31.2
[NULL] | 40000 | [NULL] | 31.2
[NULL] | 80000 | England | 31.6
[NULL] | 80000 | [NULL] | 31.6
[NULL] | 100000 | England | 30.7
[NULL] | 100000 | [NULL] | 30.7
[NULL] | 180000 | Netherlands | 37.4
[NULL] | 180000 | [NULL] | 37.4
[NULL] | 200000 | Algeria | 24.3
[NULL] | 200000 | Egypt | 36.2
[NULL] | 200000 | Netherlands | 36.8
[NULL] | 200000 | Spain | 35.1
[NULL] | 200000 | [NULL] | 132.4
[NULL] | 220000 | Argentina | 33.9
[NULL] | 220000 | [NULL] | 33.9
[NULL] | 250000 | Belgium | 34
[NULL] | 250000 | England | 68.9
[NULL] | 250000 | Spain | 30.5
[NULL] | 250000 | [NULL] | 133.4
[NULL] | 350000 | Germany | 32.7
[NULL] | 350000 | [NULL] | 32.7
[NULL] | 352000 | Argentina | 26.6
[NULL] | 352000 | [NULL] | 26.6
[NULL] | 378000 | France | 29.4
[NULL] | 378000 | [NULL] | 29.4
[NULL] | 410000 | Chile | 31.5
[NULL] | 410000 | [NULL] | 31.5
[NULL] | 418000 | Netherlands | 35.3
[NULL] | 418000 | [NULL] | 35.3
[NULL] | [NULL] | Algeria | 24.3
[NULL] | [NULL] | Argentina | 60.5
[NULL] | [NULL] | Belgium | 34
[NULL] | [NULL] | Chile | 31.5
[NULL] | [NULL] | Egypt | 36.2
[NULL] | [NULL] | England | 162.4
[NULL] | [NULL] | France | 29.4
[NULL] | [NULL] | Germany | 32.7
[NULL] | [NULL] | Netherlands | 109.5
[NULL] | [NULL] | Spain | 65.6
[NULL] | [NULL] | [NULL] | 586.1
(89 rows)Parent topic:GROUP BY Clause