Why ROLLUP and CUBE for hierarchical totals in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could get all your sales summaries in one simple step, without any manual adding?

The Scenario

Imagine you run a store and want to see sales totals by category, then by category and subcategory, and finally the grand total. Doing this by hand means adding up many numbers from different lists and spreadsheets.

The Problem

Manually summing totals for each level is slow and easy to mess up. You might forget a category or add numbers twice. It's hard to keep track and update when new sales come in.

The Solution

ROLLUP and CUBE let the database do all the adding for you. They automatically create totals for each level of detail you want, so you get all the sums in one simple query.

Before vs After

✗ Before

SELECT category, subcategory, SUM(sales) FROM sales_data GROUP BY category, subcategory;
-- Then manually add totals for categories and grand total

✓ After

SELECT category, subcategory, SUM(sales) FROM sales_data GROUP BY ROLLUP(category, subcategory);

What It Enables

You can quickly get detailed and summary totals together, making it easy to understand your data at every level.

Real Life Example

A store manager sees sales by product type, then by product type and brand, plus overall sales, all in one report without extra work.

Key Takeaways

Manual totals are slow and error-prone.

ROLLUP and CUBE automate hierarchical totals in one query.

This saves time and gives clear, complete summaries.