Reports rarely stop at detail rows. A sales report wants a subtotal per region and a grand total at the bottom; an inventory report wants totals per warehouse and overall. Instead of writing several queries and gluing them together with UNION ALL, Oracle SQL computes all those levels in one pass with GROUP BY ROLLUP.
This guide shows how ROLLUP builds subtotals, how to read the rows it adds, and how to sort and label them.
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 rollup (expr [, ...])
ROLLUP (a, b) returns the groups of (a, b), a subtotal for each a, and a grand total. With n expressions it produces n + 1 levels, rolling up from the right: ROLLUP (a, b, c) gives (a, b, c), (a, b), (a), and ().
Subtotals by Region and Country
This query counts airports by region and country for Asia and Europe, with a subtotal per region and a grand total.
Example:
select c.region, a.country_code, count(*) as airports
from airports a join countries c on c.country_code = a.country_code
where c.region in ('Asia', 'Europe')
group by rollup (c.region, a.country_code)
order by c.region, a.country_code;Output:
REGION COUNTRY_CODE AIRPORTS
_________ _______________ ___________
Asia HK 1
Asia IN 3
Asia JP 1
Asia NP 1
Asia SG 1
Asia 7
Europe DE 1
Europe FR 1
Europe GB 1
Europe NL 1
Europe TR 1
Europe 5
12
13 rows selected.Read the result from top to bottom:
- Rows with both a region and a country are the detail groups, such as three airports in India.
- The row Asia with an empty country is the subtotal for Asia: seven airports. The column that was rolled up, country_code, is NULL in that row.
- The last row, with both columns NULL, is the grand total: twelve airports.
ORDER BY c.region, a.country_code puts each subtotal right after its details, because NULL sorts last in ascending order by default.
The Order of Expressions Matters
ROLLUP treats its list as a hierarchy, from the broadest level to the most detailed. ROLLUP (c.region, a.country_code) gives subtotals per region. Reversing it to ROLLUP (a.country_code, c.region) would give subtotals per country instead, which is meaningless here since a country belongs to one region. List the expressions from the top of the hierarchy down: year, month, day; or region, country, city.
Partial Rollup
You can mix ordinary grouping expressions with ROLLUP. In GROUP BY a, ROLLUP (b, c), the column a is always grouped, and only b and c are rolled up. The result has totals within each a, but no grand total across all of them.
Example:
select c.region, a.country_code, a.city, count(*) as airports from airports a join countries c on c.country_code = a.country_code where c.region = 'Asia' group by c.region, rollup (a.country_code, a.city) order by c.region, a.country_code, a.city;
Output:
REGION COUNTRY_CODE CITY AIRPORTS _________ _______________ ____________ ___________ Asia HK Hong Kong 1 Asia HK 1 Asia IN Bengaluru 1 Asia IN Delhi 1 Asia IN Mumbai 1 Asia IN 3 Asia JP Tokyo 1 Asia JP 1 Asia NP Kathmandu 1 Asia NP 1 Asia SG Singapore 1 Asia SG 1 Asia 7 13 rows selected.
Every row keeps its region, because region is outside the ROLLUP. There is a subtotal per country and a total for Asia, but no row with an empty region.
Label the Subtotal Rows
A NULL in a subtotal row looks the same as a real NULL in the data. The GROUPING function returns 1 when its argument was rolled up in a row, so you can replace the NULL with a label such as All, as in case grouping(c.region) when 1 then 'All regions' else c.region end.
Related Guides
Conclusion
GROUP BY ROLLUP adds hierarchical subtotals and a grand total to a grouped query in a single pass. List the expressions from the broadest level to the most detailed, sort so subtotals follow their details, and use GROUPING to label the rows where columns were rolled up.
