Grouping Sets and Rollups in SQL

Image Source

Summarizing large result sets is a normal part of reporting, dashboards, exports, finance work, and ad hoc query work. The hard part starts when one report needs detailed rows, subtotal rows at more than one level, and a grand total in the same result. Writing a separate GROUP BY query for every total level can get repetitive fast, and it also makes the SQL harder to read. SQL solves that with grouping extensions that let one query return several grouped levels at the same time. That is where GROUPING SETS, ROLLUP, and CUBE come in. They all return grouped output, but they do not produce the same structure. ROLLUP follows a hierarchy, CUBE returns every possible grouping combination, and GROUPING SETS lets you state the exact grouped levels you want. Major database engines support these features a little differently, so those differences matter when you want one query to work well across more than one system.

How Multi Level Grouping Works in One Query

Grouped extensions build on the same foundation as plain GROUP BY, but they remove the limit of returning only one grouping level from a query block. Regular grouping divides rows by the selected columns and applies aggregate functions such as SUM, COUNT, or AVG to each group. GROUPING SETS, ROLLUP, and CUBE keep that same aggregate process, but they let the database return multiple grouped levels in one result. That change is what makes multi-level totals possible without stacking separate grouped queries.

Why Plain GROUP BY Stops Short

Plain GROUP BY works nicely when the report asks for a single grouping level and nothing more. Group order rows by sales_year and region, and the result gives a row per year and region pair. That answers a reporting question well, but it does not also return yearly totals by themselves or a full grand total.

This query gives a good starting point:

SELECT
    sales_year,
    region,
    SUM(net_amount) AS total_net_amount
FROM order_summary
GROUP BY sales_year, region
ORDER BY sales_year, region;

In that code, the query returns totals for each sales_year and region pair. If the report also needs a row per year across all regions, plain GROUP BY cannot add that second grouped level automatically. You would need a separate grouped query.

Repetition starts to grow at that point. The first grouped query answers the detailed level. The second answers the higher total. The third answers the grand total. The output can still be built, but the SQL now has to repeat the source table, repeat the filter logic, and repeat the aggregate expression. As subtotal levels grow, that repetition spreads across more of the statement.

In this kind of query we can see what that looks like:

SELECT
    sales_year,
    region,
    SUM(net_amount) AS total_net_amount
FROM order_summary
GROUP BY sales_year, region

UNION ALL

SELECT
    sales_year,
    NULL AS region,
    SUM(net_amount) AS total_net_amount
FROM order_summary
GROUP BY sales_year

UNION ALL

SELECT
    NULL AS sales_year,
    NULL AS region,
    SUM(net_amount) AS total_net_amount
FROM order_summary;

That works because each SELECT returns a single grouping level, and UNION ALL stacks those results into a single output. Still, the statement gets harder to scan because the grouping intent is spread across three query blocks instead of living in a single grouped clause. It also raises the chance of drift. If a filter is added to some branch but missed in another, the totals change.

Grouped extensions solve that by keeping the grouped levels in a single GROUP BY clause. The same source rows are still being summarized, but the grouping logic becomes more direct. Instead of saying run three different grouped queries and stack them, you say return these grouped levels from this grouped statement.

Something else worth mentioning is that plain GROUP BY always returns rows at a single granularity for that query block. It does not carry a built-in idea of subtotal rows. That is why subtotal features had to be added as separate syntax rather than folded into normal grouping behavior.

GROUPING SETS, ROLLUP and CUBE

The general idea behind all three features is the same, a grouped statement can return more than a single grouping level. The difference is in how those levels are chosen. GROUPING SETS is the most direct form. You name the grouping levels you want, and the database returns those levels and no others. That makes it a good fit when the report has a fixed subtotal layout.

This version asks for exactly three grouped levels:

SELECT
    department,
    job_title,
    SUM(base_salary) AS total_salary
FROM employee_payroll
GROUP BY GROUPING SETS
(
    (department, job_title),
    (department),
    ()
)
ORDER BY department, job_title;

Here, the query asks for three grouped levels. The first grouping set returns totals by department and job_title. The second returns totals by department alone. The empty grouping set () returns the grand total across the whole input. That empty set matters because it tells the database to aggregate all remaining rows into a final summary row.

ROLLUP is shorter when the grouped levels follow a hierarchy. Hierarchy means the grouped columns naturally move from more detailed to less detailed in left to right order. If a report groups by year, quarter, and month, that order already forms a ladder from detailed rows up to broader totals. ROLLUP follows that ladder by removing grouping columns from right to left.

Take a query like this:

SELECT
    sales_year,
    sales_quarter,
    sales_month,
    SUM(net_amount) AS total_net_amount
FROM monthly_sales
GROUP BY ROLLUP(sales_year, sales_quarter, sales_month)
ORDER BY sales_year, sales_quarter, sales_month;

The single ROLLUP clause expands into grouped levels that match the column order. It returns totals by year, quarter, and month, then year and quarter, then year, then the full grand total. Put plainly, the placement of columns inside ROLLUP matters. Swap them, and you change the subtotal path.

Left to right order deserves a little extra attention because it explains a lot of potential confusion. ROLLUP(region, store) produces store totals inside each region, then region totals, then the grand total. ROLLUP(store, region) produces region totals inside each store, which is a very different report. The function is not reading business meaning from the names. It is following column order. CUBE goes wider than ROLLUP. Instead of following a single hierarchy, it returns every possible grouping combination from the listed expressions. With two grouped columns, that means four grouped levels. With three grouped columns, that means eight grouped levels. Row counts can grow fast, which is why CUBE fits best when every subtotal combination is actually useful to the report.

We can see that wider grouping style here:

SELECT
    warehouse_region,
    shipping_method,
    SUM(package_count) AS total_packages
FROM shipment_facts
GROUP BY CUBE(warehouse_region, shipping_method)
ORDER BY warehouse_region, shipping_method;

That query returns totals for warehouse_region and shipping_method pairs, totals by warehouse_region, totals by shipping_method, and a grand total. Notice what makes CUBE different from ROLLUP. ROLLUP(warehouse_region, shipping_method) would not return totals by shipping_method alone. CUBE does, because it includes every grouping combination across the listed columns.

Small side by side comparisons can also make the distinction easier to see. These two queries look close, but they do not ask for the same grouped result:

SELECT
    campus,
    course_level,
    COUNT(*) AS course_count
FROM class_schedule
GROUP BY ROLLUP(campus, course_level);

That ROLLUP returns grouped rows for campus and course_level, then campus, then the grand total.

SELECT
    campus,
    course_level,
    COUNT(*) AS course_count
FROM class_schedule
GROUP BY CUBE(campus, course_level);

And this CUBE returns the same grouped rows as the rollup query, but it also adds totals by course_level alone across all campuses.

GROUPING SETS can match either form when you want explicit control. If a report needs the detailed rows, totals by campus, and a grand total, but does not need totals by course level alone, then GROUPING SETS can ask for just that set of grouped levels and skip the rest.

A report like that can be written like this:

SELECT
    campus,
    course_level,
    COUNT(*) AS course_count
FROM class_schedule
GROUP BY GROUPING SETS
(
    (campus, course_level),
    (campus),
    ()
);

This is part of why GROUPING SETS reads well in reporting SQL. The grouped levels are written directly in the query, almost like a compact report outline.

All three forms still depend on regular aggregate logic. You still choose grouped columns. You still choose aggregate expressions such as SUM or COUNT. The change is that the grouped clause can now return multi level summaries in a single result set instead of stopping at a single granularity.

Writing Better Subtotal Queries

Getting multi-level totals into a result set is only part of the work. Reports also need to stay readable after subtotal rows appear. Query-writing choices matter more at that stage. Results can be mathematically right and still feel awkward to read if subtotal rows blur into detail rows, if the grouping form adds levels nobody asked for, or if the SQL relies on syntax that does not travel well between engines. Good subtotal queries keep the output readable while still matching the report layout the query is trying to produce.

Labeling Subtotal Rows

Subtotal rows usually place NULL into grouped columns that are no longer active at that subtotal level. That creates a practical problem right away. Source data can already contain NULL, so subtotal rows with NULL in columns such as region or product_line do not explain themselves. Without a helper function, the output can leave readers unsure which NULL came from missing data and which NULL marks an all-values subtotal.

Readable reports normally start by turning those subtotal markers into text labels people can scan quickly. CASE works well with GROUPING() for that job:

SELECT
    CASE
        WHEN GROUPING(region) = 1 THEN 'All Regions'
        ELSE region
    END AS region_label,
    CASE
        WHEN GROUPING(product_line) = 1 THEN 'All Product Lines'
        ELSE product_line
    END AS product_line_label,
    SUM(revenue) AS total_revenue
FROM sales_fact
GROUP BY ROLLUP(region, product_line)
ORDER BY region_label, product_line_label;

For that, the result reads much better than a report full of unlabeled NULL values. It also keeps actual NULL data separate from subtotal placeholders, which matters when results are exported or passed to charts, spreadsheets, or later review. Row order matters too. Subtotal rows can end up mixed into detail rows in ways that make a report feel scrambled. In engines that support GROUPING_ID(), that function can give you a numeric grouping level that helps keep row order stable:

SELECT
    region,
    product_line,
    SUM(revenue) AS total_revenue,
    GROUPING_ID(region, product_line) AS grouping_level
FROM sales_fact
GROUP BY ROLLUP(region, product_line)
ORDER BY grouping_level, region, product_line;

That ordering tends to place detail rows first, then subtotal rows, then the grand total, which is usually easier to scan than relying on whatever order falls out of the grouped result. Engines without GROUPING_ID() can still produce readable output with GROUPING() inside ORDER BY, though the query gets a little longer.

Label text itself should stay plain. Grand Total, All Regions, All Departments, or similar wording usually works better than labels that force the reader to stop and decode the row. Reports stay easier to follow when subtotal labels match the business language already used in the rest of the output.

Picking the Right Form

Choosing between ROLLUP, CUBE, and GROUPING SETS comes down to the report layout you need. ROLLUP fits best when grouped columns follow a hierarchy. Time-based reporting is a common case because grouped levels usually move from detailed parts up to broader totals. Year, quarter, and month already form that ladder, so ROLLUP reads naturally there.

This kind of query is a good fit for hierarchical totals:

SELECT
    fiscal_year,
    fiscal_quarter,
    fiscal_month,
    SUM(expense_amount) AS total_expense
FROM expense_summary
GROUP BY ROLLUP(fiscal_year, fiscal_quarter, fiscal_month);

In that query, it asks for month-level detail, then quarter totals, then year totals, then the grand total. SQL stays fairly short because the subtotal path is implied by the column order.

CUBE fits a different kind of report. Rather than following a single hierarchy, it returns every grouping combination across the listed columns. That works well when each grouped column can stand on its own as a reporting dimension. Regional totals, channel totals, and product-family totals may all be needed separately, along with the detailed combinations.

That same strength can also produce more output than the report needs. Each added grouped expression raises the number of subtotal combinations, so CUBE is best saved for reports where those combinations are actually wanted. If the report only needs selected subtotal levels, GROUPING SETS is usually the better choice.

Take a query like this:

SELECT
    channel,
    subscription_tier,
    billing_cycle,
    COUNT(*) AS account_count
FROM subscription_accounts
GROUP BY GROUPING SETS
(
    (channel, subscription_tier, billing_cycle),
    (channel, subscription_tier),
    (channel),
    ()
);

That query asks for exactly the subtotal levels needed and nothing extra. That makes GROUPING SETS a strong choice when the report layout is fixed ahead of time and only certain subtotal levels belong in the output.

In short, ROLLUP fits ladder-like totals, CUBE fits every-combination summaries, and GROUPING SETS fits hand-picked subtotal layouts. Picking the form that matches the report keeps the output smaller and keeps the SQL easier to follow later.

Portability Across Major Engines

Support across database engines is close in some places and uneven in others. PostgreSQL, SQL Server, and Oracle support GROUPING SETS, ROLLUP, and CUBE. SQL Server also supports helper functions such as GROUPING() and GROUPING_ID(). Oracle supports both GROUPING() and GROUPING_ID(), and PostgreSQL supports GROUPING(). That gives those three engines fairly similar coverage for subtotal query work, though the helper function details are not identical across them.

MySQL is where portability questions show up faster. Standard MySQL centers more on WITH ROLLUP, so queries written with GROUPING SETS or CUBE do not travel across plain MySQL deployments the same way they do across PostgreSQL, SQL Server, and Oracle. That matters when the same reporting query has to run on more than one backend.

That difference changes how portable SQL should be written. Codebases targeting PostgreSQL, SQL Server, and Oracle can use the full family more freely. When MySQL is part of the target set, the safe subset gets smaller, and subtotal queries may need to rely more on ROLLUP or fall back to UNION ALL for wider compatibility.

Helper functions matter too. SQL Server has both GROUPING() and GROUPING_ID(), which makes labeling and sorting subtotal rows easier. PostgreSQL and Oracle cover the basic grouping marker function, but not every helper is shared the same way across engines. Query text that feels tidy in one engine can need small rewrites in another.

Portability also goes past syntax. Large generated reports can push engine-specific grouping limits or produce more subtotal rows than expected. That is worth keeping in mind when query builders or report layers build grouped SQL automatically instead of by hand.

Direct Alternatives

Grouped extensions are not the only way to write subtotal queries. The most direct fallback is still multiple grouped SELECT statements combined with UNION ALL. That form is longer, but it is familiar SQL and travels well because it only depends on regular grouping and set operations.

This version shows the manual form:

SELECT
    division,
    team_name,
    SUM(ticket_count) AS total_tickets
FROM support_volume
GROUP BY division, team_name

UNION ALL

SELECT
    division,
    NULL AS team_name,
    SUM(ticket_count) AS total_tickets
FROM support_volume
GROUP BY division

UNION ALL

SELECT
    NULL AS division,
    NULL AS team_name,
    SUM(ticket_count) AS total_tickets
FROM support_volume;

In that, the query can produce the same kind of result you would ask from GROUPING SETS. The tradeoff is repetition. Filters, joins, computed expressions, and business rules have to stay aligned across every branch, which leaves more room for drift when somebody edits the query later.

Window functions are another nearby option, but they solve a different problem. Window aggregates attach totals to detail rows without collapsing the result into grouped subtotal rows. That is useful when a report needs every detail row to stay visible while still displaying summary values beside it.

We can see this kind of query in action:

SELECT
    division,
    team_name,
    ticket_id,
    opened_at,
    COUNT(*) OVER (PARTITION BY division) AS division_ticket_total
FROM support_tickets;

That query keeps every ticket row and adds a division total next to it. Useful output, but not the same thing as subtotal rows returned by ROLLUP or GROUPING SETS.

Summary tables and materialized views also belong in the larger discussion. Those options fit recurring reporting workloads where the same subtotal-heavy queries are read repeatedly. That choice is less about subtotal syntax and more about where the summary work should happen, at query time or ahead of time.

Conclusion

GROUPING SETS, ROLLUP, and CUBE all build on normal GROUP BY, but they change what a grouped query can return by letting one statement produce more than one aggregation level. That lets the database generate detail totals, subtotal rows, and a grand total from the same grouped operation instead of forcing separate queries to be stacked by hand. When viewed mechanically, the main difference comes down to how grouping levels are selected, how subtotal rows are marked, and how far the syntax travels across database engines.

1. PostgreSQL GROUP BY and Grouping Sets Documentation

2. SQL Server SELECT GROUP BY Documentation

3. SQL Server GROUPING Documentation

4. SQL Server GROUPING_ID Documentation

5. Oracle Database SELECT Documentation

6. Oracle Database GROUPING Documentation

7. MySQL GROUP BY Modifiers and WITH ROLLUP Documentation

Image Source