Product DocumentationAppearance
GROUPING
Return an integer bit mask that identifies which set a row belongs to in the results of a query that uses GROUP BY extensions: GROUPING SETS, CUBE, or ROLLUP.
GROUPING(group_by_expression [ ,...])The expressions for the GROUPING function must match, or be a subset of, the expressions used in the GROUP BY GROUPING SETS, GROUP BY CUBE, or GROUP BY ROLLUP clause.
For each grouped row in the result of the query, the GROUPING function assigns a bit to each GROUP BY expression. These bit values are converted to and displayed as their corresponding integer values (for example, 00 = 0, 01 = 1, 10 = 2, 11 = 3, and so on). A return value of 0 means that the expression in the GROUPING function is included in the grouping criteria for that row. A return value of 1 means that the expression in the GROUPING function is not included.
GROUP BY CUBE and GROUP BY ROLLUP queries return higher numbers, such as 3 (11) for their subtotal and grand total rows, given that none of the grouping criteria apply to those rows.
Because rows in queries that use GROUP BY extensions may be identified in this way, you can filter query results in the HAVING clause based on values returned by GROUPING functions. For example, you can return only rows with 0, return only subtotal rows, or filter out subtotal and grand total rows.
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 uses the function on one column, position.
premdb=# select position, cob, grouping(position), sum(weekly_wages)
from player
group by grouping sets(position, cob)
order by 1, 2, 3;
position | cob | grouping | sum
----------+-------------+----------+---------
D | [NULL] | 0 | 180000
F | [NULL] | 0 | 1762000
G | [NULL] | 0 | 200000
M | [NULL] | 0 | 2186000
[NULL] | Algeria | 1 | 200000
[NULL] | Argentina | 1 | 572000
[NULL] | Belgium | 1 | 250000
[NULL] | Chile | 1 | 410000
[NULL] | Egypt | 1 | 200000
[NULL] | England | 1 | 720000
[NULL] | France | 1 | 378000
[NULL] | Germany | 1 | 350000
[NULL] | Netherlands | 1 | 798000
[NULL] | Spain | 1 | 450000
[NULL] | [NULL] | 0 | [NULL]
[NULL] | [NULL] | 1 | [NULL]
(16 rows)This example uses the function on one columns position and cob.
premdb=# select position, cob, grouping(position, cob), sum(weekly_wages)
from player
group by grouping sets(position, cob)
order by 1, 2, 3;
position | cob | grouping | sum
----------+-------------+----------+---------
D | [NULL] | 1 | 180000
F | [NULL] | 1 | 1762000
G | [NULL] | 1 | 200000
M | [NULL] | 1 | 2186000
[NULL] | Algeria | 2 | 200000
[NULL] | Argentina | 2 | 572000
[NULL] | Belgium | 2 | 250000
[NULL] | Chile | 2 | 410000
[NULL] | Egypt | 2 | 200000
[NULL] | England | 2 | 720000
[NULL] | France | 2 | 378000
[NULL] | Germany | 2 | 350000
[NULL] | Netherlands | 2 | 798000
[NULL] | Spain | 2 | 450000
[NULL] | [NULL] | 1 | [NULL]
[NULL] | [NULL] | 2 | [NULL]
(16 rows)This example uses the GROUPING function to identify results returned by GROUP BY ROLLUP. The rows that return 0 are those rows that can be grouped by both position and cob. The rows that return 1 are subtotal rows.
premdb=# select position, cob, grouping(position, cob), sum(weekly_wages)
from player
group by rollup(position, cob)
order by 1, 2, 3;
position | cob | grouping | sum
----------+-------------+----------+---------
D | Netherlands | 0 | 180000
D | NULL | 1 | 180000
F | Argentina | 0 | 572000
F | Chile | 0 | 410000
F | Egypt | 0 | 200000
F | England | 0 | 580000
F | NULL | 1 | 1762000
G | Spain | 0 | 200000
G | NULL | 1 | 200000
M | Algeria | 0 | 200000
M | Belgium | 0 | 250000
M | England | 0 | 140000
M | France | 0 | 378000
M | Germany | 0 | 350000
M | Netherlands | 0 | 618000
M | Spain | 0 | 250000
M | NULL | 1 | 2186000
NULL | NULL | 0 | NULL
NULL | NULL | 1 | NULL
NULL | NULL | 3 | 4328000
(20 rows)