ROLLUP vs CUBE in SQL Server - The GROUP BY Trick Most Developers Miss

Suppose you need totals at multiple levels.

You could write several GROUP BY queries and combine them with UNION ALL.

Or you can let SQL Server generate them for you.

That's where ROLLUP and CUBE become useful.

1. Normal GROUP BY

Imagine:

Sales
--------------------------------
Region   Year   Product   Amount
West     2025   Laptop     1000
West     2025   Phone       500
East     2025   Laptop     1200
East     2026   Laptop     1500
East     2026   Phone       700

Normal query:

SELECT Region, Year, Product, SUM(Amount) AS Total
FROM Sales
GROUP BY Region, Year, Product;

You only get the detailed rows.

But what if you also need:

Region + Year + Product
Region + Year
Region
Grand Total

2. The painful way

Without ROLLUP, you might write:

SELECT Region, Year, Product, SUM(Amount)
FROM Sales
GROUP BY Region, Year, Product

UNION ALL

SELECT Region, Year, NULL, SUM(Amount)
FROM Sales
GROUP BY Region, Year

UNION ALL

SELECT Region, NULL, NULL, SUM(Amount)
FROM Sales
GROUP BY Region

UNION ALL

SELECT NULL, NULL, NULL, SUM(Amount)
FROM Sales;

That's a lot of SQL.

3. ROLLUP - Let SQL Server do it

SELECT
    Region,
    Year,
    Product,
    SUM(Amount) AS Total
FROM Sales
GROUP BY ROLLUP(Region, Year, Product);

SQL Server generates:

Region + Year + Product
Region + Year
Region
Grand Total

Think of it as moving up a hierarchy:

Region
  ↓
Year
  ↓
Product

Important: column order matters

ROLLUP(Region, Year, Product)

is different from:

ROLLUP(Product, Year, Region)

ROLLUP follows the order you provide.

4. CUBE - Give me every combination

What if there is no hierarchy?

For:

Region
Device
Browser

you might need:

Region + Device + Browser
Region + Device
Region + Browser
Device + Browser
Region
Device
Browser
Grand Total

Instead of writing all those queries:

SELECT
    Region,
    Device,
    Browser,
    COUNT(*) AS Errors
FROM ErrorLogs
GROUP BY CUBE(Region, Device, Browser);

Easy rule

ROLLUP = hierarchical combinations

CUBE   = every possible combination

5. How many queries are we avoiding?

For:

ROLLUP(A, B, C)

you get:

ABC
AB
A
Total

That's 4 levels.

For:

CUBE(A, B, C)

you get:

ABC
AB
AC
BC
A
B
C
Total

That's:

2³ = 8 combinations

With 5 dimensions:

2⁵ = 32 combinations

Imagine maintaining 32 GROUP BY queries with UNION ALL.

That's the real power of CUBE.

6. Real-world use case #1 - Users

Suppose:

Department
Role
User

You need:

Department + Role + User
Department + Role
Department
Total

Use:

SELECT Department, Role, COUNT(*) AS UserCount
FROM Users
GROUP BY ROLLUP(Department, Role);

7. Real-world use case #2 - Orders

Suppose:

Country
State
PaymentMethod

You need orders at every geographical level:

SELECT
    Country,
    State,
    PaymentMethod,
    COUNT(*) AS Orders
FROM Orders
GROUP BY ROLLUP(Country, State, PaymentMethod);

No separate queries for country, state, and payment totals.

8. Real-world use case #3 - Inventory

Warehouse
Category
Product

You need:

Warehouse + Category + Product
Warehouse + Category
Warehouse
Total

SELECT
    Warehouse,
    Category,
    Product,
    SUM(Quantity) AS Quantity
FROM Inventory
GROUP BY ROLLUP(Warehouse, Category, Product);

9. Real-world use case #4 - API traffic

Suppose your logs contain:

Service
Endpoint
HTTPMethod

You want to investigate:

Which service or endpoint is receiving the most traffic?

SELECT
    Service,
    Endpoint,
    HTTPMethod,
    COUNT(*) AS Requests
FROM ApiLogs
GROUP BY ROLLUP(Service, Endpoint, HTTPMethod);

One query gives detail, endpoint totals, service totals, and overall traffic.

10. Real-world use case #5 - Project / Construction data

Imagine:

Project
Contract
Material

You need cost at every level:

SELECT
    ProjectId,
    ContractId,
    MaterialId,
    SUM(Cost) AS TotalCost
FROM MaterialCost
GROUP BY ROLLUP(ProjectId, ContractId, MaterialId);

You get:

Project + Contract + Material
Project + Contract
Project
Grand Total

11. The NULL problem

You may see:

West | 2025 | 1500
West | NULL | 1500
NULL | NULL | 4900

Does NULL mean the original data was NULL?

Or is it a subtotal generated by ROLLUP?

Use:

GROUPING()

Example:

SELECT
    Region,
    Year,
    SUM(Amount) AS Total,
    GROUPING(Region) AS IsRegionTotal,
    GROUPING(Year) AS IsYearTotal
FROM Sales
GROUP BY ROLLUP(Region, Year);

For multiple columns, GROUPING_ID() is even more useful:

SELECT
    Region,
    Year,
    Product,
    SUM(Amount) AS Total,
    GROUPING_ID(Region, Year, Product) AS GroupLevel
FROM Sales
GROUP BY ROLLUP(Region, Year, Product);

This lets your application distinguish:

Detail
Subtotal
Grand Total

12. ROLLUP or CUBE?

Use ROLLUP

When there is a natural hierarchy:

Country → State → City
Category → Product
Project → Contract → Material

GROUP BY ROLLUP(A, B, C)

Use CUBE

When you genuinely need different combinations:

Region
Device
Browser

GROUP BY CUBE(Region, Device, Browser)

Question

What is the difference between ROLLUP and CUBE?

Answer:

ROLLUP generates hierarchical subtotals based on column order.

CUBE generates all possible combinations of the grouping columns.

ROLLUP(A,B,C)

ABC
AB
A
Total

CUBE(A,B,C)

ABC
AB
AC
BC
A
B
C
Total

The Trick to Remember

Don't think:

"ROLLUP and CUBE are reporting features."

Think:

"They can replace a pile of GROUP BY + UNION ALL queries."

If you find yourself writing:

GROUP BY A, B, C

UNION ALL

GROUP BY A, B

UNION ALL

GROUP BY A

UNION ALL

-- Total

Think:

GROUP BY ROLLUP(A, B, C)

And if you need every possible combination:

GROUP BY CUBE(A, B, C)

That's the trick.