How to Use GROUPING SETS in Oracle SQL

ROLLUP and CUBE produce fixed patterns of subtotals. Sometimes you need a different selection: staff counted by department and, separately, by base airport, plus a total, without the department-and-airport detail. GROUPING SETS lets you list exactly the groupings you want, and Oracle computes them all in one query.

This guide covers the GROUPING SETS clause and the three functions that identify its rows: GROUPING, GROUPING_ID, and GROUP_ID.

Code for This Guide

The main examples are in the examples/grouping folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

group by grouping sets ((expr, ...), (expr, ...), ..., ())

grouping(expr)
grouping_id(expr [, ...])
group_id()

Each parenthesized list is one grouping, and () is the grand total. ROLLUP and CUBE are shorthands for particular grouping sets: ROLLUP (a, b) equals GROUPING SETS ((a, b), (a), ()), and CUBE (a, b) adds (b).

Staff by Department, by Airport, and in Total

This query counts employees of departments 20 and 40 in three independent groupings.

Example:

select e.department_id, e.base_airport, count(*) as staff
from   employees e
where  e.department_id in (20, 40)
group  by grouping sets ((e.department_id), (e.base_airport), ())
order  by 1, 2;

Output:

   DEPARTMENT_ID BASE_AIRPORT       STAFF
________________ _______________ ________
              20                       12
              40                       10
                 BOM                    1
                 DXB                   15
                 LHR                    2
                 NRT                    1
                 SIN                    3
                                       22

8 rows selected.

The first two rows are the department counts, the next five the airport counts, and the last the total of 22. Each row has a value only in the column of its own grouping.

What It Replaces

Without GROUPING SETS, the same result needs one query per grouping glued together with UNION ALL, each reading the table again.

Example:

select e.department_id, null as base_airport, count(*) as staff
from   employees e where e.department_id in (20, 40) group by e.department_id
union all
select null, e.base_airport, count(*)
from   employees e where e.department_id in (20, 40) group by e.base_airport
union all
select null, null, count(*)
from   employees e where e.department_id in (20, 40)
order  by 1, 2;

Output:

   DEPARTMENT_ID BASE_AIRPORT       STAFF
________________ _______________ ________
              20                       12
              40                       10
                 BOM                    1
                 DXB                   15
                 LHR                    2
                 NRT                    1
                 SIN                    3
                                       22

8 rows selected.

The rows are identical, but the GROUPING SETS version is shorter, keeps the filter in one place, and lets the optimizer compute all groupings from one pass over the data.

Label Rows with GROUPING and GROUPING_ID

A NULL in a subtotal row could also be a real NULL in the data. GROUPING (expr) returns 1 when expr was rolled up in a row and 0 otherwise. GROUPING_ID (a, b, ...) combines those bits into one number that identifies the level: with two columns, 0 is detail, 1 means the second column was rolled up, 2 the first, and 3 both.

The first query below labels the subtotal rows of a ROLLUP. The second shows GROUP_ID, which numbers duplicate groupings: listing the same grouping twice returns each group twice, numbered 0 and 1.

Example:

select case grouping(c.region) when 1 then 'All regions' else c.region end as region,
       case grouping(a.country_code) when 1 then 'All' else a.country_code end as country,
       grouping_id(c.region, a.country_code) as gid,
       count(*) as airports
from   airports a join countries c on c.country_code = a.country_code
where  c.region in ('Oceania', 'Africa')
group  by rollup (c.region, a.country_code);

select a.country_code, count(*) as airports, group_id() as gid
from   airports a
where  a.country_code in ('IN', 'US')
group  by grouping sets ((a.country_code), (a.country_code));

Output:

REGION         COUNTRY       GID    AIRPORTS
______________ __________ ______ ___________
Oceania        AU              0           1
Africa         KE              0           1
Oceania        NZ              0           1
Africa         ZA              0           1
Oceania        All             1           2
Africa         All             1           2
All regions    All             3           4

7 rows selected.

COUNTRY_CODE       AIRPORTS    GID
_______________ ___________ ______
IN                        3      0
US                        3      0
IN                        3      1
US                        3      1

Duplicate groupings rarely appear on purpose, but they can arise when you combine ROLLUP and CUBE in one GROUP BY. Adding having group_id() = 0 removes the copies.

Related Guides

• How to Add Subtotals with ROLLUP in Oracle SQL
• How to Summarize Every Combination with CUBE in Oracle SQL
• How to Filter Groups with HAVING in Oracle SQL

Conclusion

GROUPING SETS computes exactly the groupings you list, including the grand total (), in a single query that replaces a chain of UNION ALL queries. Use GROUPING and GROUPING_ID to tell subtotal rows from detail rows and to label them, and GROUP_ID to find and remove duplicate groupings.