[SQL] Mastering GROUP BY: Understanding ROLLUP, CUBE, and GROUPING() in Oracle SQL

When generating reports in Oracle SQL, one of the biggest challenges is calculating subtotals and grand totals dynamically. Manually writing multiple GROUP BY queries for different levels of aggregation can be tedious and inefficient.

Luckily, Oracle SQL provides ROLLUP and CUBE, two powerful extensions of GROUP BY that simplify multi-level aggregations. But how do you differentiate actual data rows from subtotal rows? That’s where GROUPING() comes into play!

In this blog post, we’ll explore:
✅ ROLLUP – For hierarchical subtotals (e.g., department-wise totals).
✅ CUBE – For all possible subtotals (e.g., department-wise and category-wise totals).
✅ GROUPING() – To differentiate between actual data and subtotal rows.

Let’s dive into real-world examples to see how these powerful SQL features can enhance your reporting! 🚀

ROLLUP

In Oracle SQL, the ROLLUP extension is used with the GROUP BY clause to generate subtotals and a grand total in a hierarchical manner. It is useful when you need aggregate results at multiple levels of a hierarchy.

Syntax:


SELECT column1, column2, ..., aggregate_function(column_n)
FROM table_name
GROUP BY ROLLUP (column1, column2, ...);

The ROLLUP function creates subtotals moving from right to left, aggregating data at each level.

Example:

Let's consider a Sales table with columns department, category, and sales_amount:

SELECT department, category, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY ROLLUP(department, category);

How It Works:

This query will return:

Sales total for each (department, category).
Sales subtotal for each department (NULL in the category column).
A grand total for all sales (NULL in both department and category).

Example Output:

CUBE

The CUBE extension in Oracle SQL is used with the GROUP BY clause to generate all possible combinations of subtotals for the specified columns, including a grand total. Unlike ROLLUP, which creates hierarchical subtotals, CUBE computes every combination of aggregations.

Syntax:


SELECT column1, column2, ..., aggregate_function(column_n)
FROM table_name
GROUP BY CUBE (column1, column2, ...);

The CUBE function creates subtotal rows for every possible grouping of the specified columns.

Example:

Let's consider a Sales table with columns department, category, and sales_amount:


SELECT department, category, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY CUBE(department, category);

How It Works:

This query will return:

Sales total for each (department, category).
Sales subtotal for each department (NULL in the category column).
Sales subtotal for each category (NULL in the department column).
A grand total (NULL in both columns).

Example Output:

Difference Between ROLLUP and CUBE

ROLLUP(col1, col2): Generates subtotals at hierarchical levels.

CUBE(col1, col2): Generates all possible combinations of subtotals.

GROUPING

The GROUPING() function helps differentiate between actual data rows and aggregated subtotal rows when using ROLLUP or CUBE. It returns:

0 if the column is part of a regular row
1 if the column is part of a subtotal or grand total row

Example:

Consider a Sales table with columns department, category, and sales_amount:


SELECT department, 
    category, 
    SUM(sales_amount) AS total_sales,
    GROUPING(department) AS is_dept_grouped,
    GROUPING(category) AS is_cat_grouped,
    GROUPING(department) + GROUPING(category) AS grouping_level
FROM sales
GROUP BY CUBE(department, category);

How It Works:

GROUPING(department) = 1 → Means subtotal or grand total row
GROUPING(category) = 1 → Means subtotal or grand total row
GROUPING(department) + GROUPING(category) helps classify the level of aggregation:
- 0 → Regular data row
- 1 → Partial subtotal (either department or category)
- 2 → Grand total (NULL in both columns)

Example Output:

Why Use `GROUPING()`?

It helps distinguish between normal rows and subtotal rows.
It enables conditional formatting in reports.
It allows filtering only subtotal or grand total rows (HAVING GROUPING(column) = 1).

The Cool Fox