Subtotals: ROLLUP, CUBE and GROUPING SETS

Reports need subtotals: runs per phase and per season and overall. Instead of several queries glued together, GROUP BY can produce several levels of grouping at once:

On subtotal rows, the rolled-up columns are null. GROUPING(col) tells you which nulls mean "subtotal": it's 1 when col was rolled up, 0 otherwise.

The test case

Runs per phase (powerplay, middle, death) for each season, with a total for each season and a grand total. Label the subtotal rows "All phases" and "All seasons".

Expected result

Way 1: ROLLUP

select case when grouping(b.season) = 1 then 'All seasons' else to_char(b.season) end as season,
       case when grouping(b.phase)  = 1 then 'All phases'  else b.phase end            as phase,
       sum(total_runs)                                                             as runs
from   (select season, total_runs,
               case when over_no <= 6 then 'Powerplay' when over_no >= 17 then 'Death' else 'Middle' end as phase
        from   cr_balls_v) b
group  by rollup (b.season, b.phase)
order  by grouping(b.season), b.season, grouping(b.phase), b.phase;

Sorting by GROUPING(...) puts each subtotal after its details and the grand total last.

Why the b. everywhere? The output columns are named season and phase too, and in ORDER BY an unqualified name means the output column first. order by grouping(season) would then mean grouping(case when grouping(season) ...), and Oracle stops with ORA-00935: group function is nested too deeply. Qualifying with the inline view's alias (b.season) means the original column. (We found this one the honest way: the test harness caught it.) Label with GROUPING, not with NVL(phase, 'All phases'): if the data itself had a null phase, NVL couldn't tell it from a subtotal.

Way 2: GROUPING SETS

select case when grouping(b.season) = 1 then 'All seasons' else to_char(b.season) end as season,
       case when grouping(b.phase)  = 1 then 'All phases'  else b.phase end            as phase,
       sum(total_runs)                                                             as runs
from   (select season, total_runs,
               case when over_no <= 6 then 'Powerplay' when over_no >= 17 then 'Death' else 'Middle' end as phase
        from   cr_balls_v) b
group  by grouping sets ((b.season, b.phase), (b.season), ())
order  by grouping(b.season), b.season, grouping(b.phase), b.phase;

Exactly what ROLLUP (season, phase) means, written out. GROUPING SETS is the general tool: want per-phase totals across both seasons too? Add (phase) to the list, which is what CUBE would do.

Way 3: UNION ALL of three queries

with balls as (
  select season, total_runs,
         case when over_no <= 6 then 'Powerplay' when over_no >= 17 then 'Death' else 'Middle' end as phase
  from   cr_balls_v
)
select season, phase, runs
from   (select to_char(season) as season, phase, sum(total_runs) as runs, 0 as lvl
        from balls group by season, phase
        union all
        select to_char(season), 'All phases', sum(total_runs), 1
        from balls group by season
        union all
        select 'All seasons', 'All phases', sum(total_runs), 2
        from balls)
order  by case when lvl = 2 then 1 else 0 end, season, lvl, phase;

The way it was done before ROLLUP: one query per level. It reads the data three times, and the sort needs a helper column (lvl) to put subtotals in the right place.

19c and 26ai

The same on both.

Gotchas

• CUBE grows fast: n columns give 2ⁿ groupings. With five columns, 32.
• Subtotal nulls vs real nulls: label with GROUPING(), never with NVL.
• GROUPING_ID(a, b) packs all the GROUPING flags into one number (a bit per column), handy for filtering: having grouping_id(season, phase) in (0, 3) keeps the details and the grand total.