How to Summarize Every Combination with CUBE in Oracle SQL

A cross-tabulation shows a measure for every combination of two dimensions, plus the totals along each margin and a grand total in the corner. Think of tickets by booking status and cabin, with a total for each status, a total for each cabin, and the overall count. GROUP BY CUBE returns exactly those rows in one query.

This guide shows how CUBE differs from ROLLUP, how to read its result, and how to keep only the totals.

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 cube (expr [, ...])

CUBE (a, b) returns four groupings: (a, b), (a), (b), and the grand total (). For n expressions it computes 2 to the power n groupings, every subset of the list.

CUBE Compared with ROLLUP

ROLLUP follows the order of its list and stops at each level of a hierarchy. CUBE ignores the order and returns totals for every dimension, which suits dimensions that are not nested, as in how to add subtotals with ROLLUP in Oracle SQL.

Tickets by Status and Cabin

This query counts tickets by booking status and cabin, with every margin.

Example:

select b.status, t.cabin, count(*) as tickets
from   bookings b join tickets t on t.booking_id = b.booking_id
group  by cube (b.status, t.cabin)
order  by b.status nulls last, t.cabin nulls last;

Output:

STATUS       CABIN          TICKETS
____________ ___________ __________
CANCELLED    BUSINESS            18
CANCELLED    ECONOMY             35
CANCELLED                        53
COMPLETED    BUSINESS           180
COMPLETED    ECONOMY            563
COMPLETED                       743
CONFIRMED    BUSINESS            72
CONFIRMED    ECONOMY            201
CONFIRMED                       273
             BUSINESS           270
             ECONOMY            799
                               1069

12 rows selected.

The twelve rows hold four kinds of group:

• Status and cabin both filled: the detail cells, such as 180 business tickets in completed bookings.
• Status filled, cabin empty: the total per status, such as 743 tickets in completed bookings.
• Status empty, cabin filled: the total per cabin, such as 270 business tickets in all.
• Both empty: the grand total, 1,069 tickets.

The query sorts with NULLS LAST on both columns so each status block ends with its total and the cabin totals come at the end.

Keep Only the Totals

GROUPING_ID returns 0 for detail rows and a positive number for rows in which any column was rolled up. Filtering on it in HAVING keeps the margins and the grand total.

Example:

select b.status, t.cabin, count(*) as tickets
from   bookings b join tickets t on t.booking_id = b.booking_id
group  by cube (b.status, t.cabin)
having grouping_id(b.status, t.cabin) > 0
order  by grouping_id(b.status, t.cabin), b.status, t.cabin;

Output:

STATUS       CABIN          TICKETS
____________ ___________ __________
CANCELLED                        53
COMPLETED                       743
CONFIRMED                       273
             BUSINESS           270
             ECONOMY            799
                               1069

6 rows selected.

Sorting by GROUPING_ID also groups the rows by level: totals per status (1), totals per cabin (2), and the grand total (3).

Things to Know

• The number of groupings doubles with each expression. CUBE over five columns computes 32 groupings, so cube only the dimensions you need.
• A NULL in a total row looks like a real NULL in the data. Use GROUPING (expr), which returns 1 for a rolled-up column, to tell them apart or to print a label such as All.
• You can mix CUBE with ordinary grouping columns: GROUP BY region, CUBE (status, cabin) computes the cube within each region.

Conclusion

GROUP BY CUBE computes every combination of its expressions, giving a full cross-tabulation with all margins and a grand total in one query. Use it for independent dimensions, use ROLLUP for hierarchies, and use GROUPING_ID to label or filter the total rows.