DEV Community

Cover image for ROLLUP vs CUBE: How I Finally Understood SQL Subtotals
Jessica Aki
Jessica Aki

Posted on

ROLLUP vs CUBE: How I Finally Understood SQL Subtotals

#sql #postgres #database #beginners

When I first ran into ROLLUP and CUBE in SQL, I genuinely thought I understood them.

I didn’t.

I knew the syntax. I could write the queries. But every time I looked at the results, they felt… random. And whenever SQL feels or seems random, it usually means I’m missing something important.

This post is me being honest about what confused me at first, where my assumptions were wrong and what finally made these concepts click

The Setup: A Real Sales Table

Let’s use something realistic: sales data.

create table sales (
  region    varchar2(20),
  product   varchar2(20),
  amount    number
);
Enter fullscreen mode Exit fullscreen mode 
insert into sales values ('West', 'Laptop', 1200);
insert into sales values ('West', 'Phone', 800);
insert into sales values ('West', 'Tablet', 600);
insert into sales values ('East', 'Laptop', 1100);
insert into sales values ('East', 'Phone', 700);
insert into sales values ('North', 'Laptop', 1000);
Enter fullscreen mode Exit fullscreen mode

Here’s what the data actually looks like:

region product amount
West Laptop 1200
West Phone 800
West Tablet 600
East Laptop 1100
East Phone 700
North Laptop 1000

You can think of this as:

  • Dimensions → region, product
  • Measure → sum(amount)

Where I Was Comfortable: Basic GROUP BY 

select region, sum(amount)
from sales
group by region;
Enter fullscreen mode Exit fullscreen mode

Result:

region sum(amount)
West 2600
East 1800
North 1000

Clear rule that made sense early on:

Every column in SELECT that isn’t aggregated must appear in GROUP BY.

No drama here.

Where Things Started to Break: Subtotals

Then I met this:

group by rollup (region, product)
Enter fullscreen mode Exit fullscreen mode

And immediately, I made an assumption.

My First Wrong Assumption

I thought:

“ROLLUP gives me all possible totals.”

So I expected:

  • totals per (region, product)
  • totals per region
  • totals per product
  • grand total

That sounds reasonable right? Well, It’s also wrong.

That actually describes CUBE, not ROLLUP.

What ROLLUP Actually Does

ROLLUP follows a hierarchy, and order matters.

select region, product, sum(amount)
from sales
group by rollup (region, product);
Enter fullscreen mode Exit fullscreen mode

SQL processes this from right to left, producing:

  1. (region, product) → detailed rows
  2. (region) → region subtotals
  3. () → grand total

That’s it.

No product-only totals. No extra combinations.

What the Results Really Mean

The output looks something like this:

region product sum(amount)
West Laptop 1200
West Phone 800
West Tablet 600
West NULL 2600
East Laptop 1100
East Phone 700
East NULL 1800
North Laptop 1000
North NULL 1000
NULL NULL 5400

This part tripped me up for a while:

NULL here does not mean missing data.
It means this column was rolled up into a subtotal.

Once I understood that, the results stopped looking random.

A Subtle Query That Forced Clarity

This one confused me more than the first:

group by region, rollup (product)
Enter fullscreen mode Exit fullscreen mode

I expected a grand total again.

But that’s not what happens.

Because:

ROLLUP only affects the columns inside it.

Generated groupings:

  • (region, product)
  • (region)

So the totals are per region, not for the whole table. That detail finally made the rules feel consistent.

Where CUBE Finally Made Sense

Once ROLLUP stopped feeling mysterious, CUBE became straightforward as it was my first misunderstanding of ROLLUPs.

select region, product, sum(amount)
from sales
group by cube (region, product);
Enter fullscreen mode Exit fullscreen mode

CUBE generates every possible combination.

For two columns, that means:

  • (region, product)
  • (region)
  • (product)
  • ()

Which gives you:

  • detailed rows
  • region totals
  • product totals
  • grand total

This is what I originally (and incorrectly) thought ROLLUP did.

How I Now Tell Them Apart

ROLLUP

  • follows a hierarchy
  • order matters
  • structured summaries

If you have N columns, you getN + 1 grouping levels.

Great for reports like:

  • day → month → year
  • region → country → global

CUBE

  • every combination
  • no hierarchy
  • more rows, more flexibility

If you have N columns, you get 2ⁿgrouping levels.

Best for:

  • analytics
  • slicing data in multiple ways
  • pivot-style reporting

Closing Thought

If ROLLUP or CUBE ever feel confusing, it’s usually because you’re asking:

“Why didn’t SQL give me this total?”

And the answer is almost always that column wasn’t rolled up or you actually wanted CUBE

Feeling stuck here is normal. So know, you're not the only one.

— Jessica Aki
Data & Database Engineering Enthusiast

I’m documenting my journey learning SQL, databases, and the systems behind real-world data platforms honestly, slowly, and in public.

Top comments (0)