Advanced SQL Grouping Sets and Rollup/Cube Patterns: A Complete Guide

Advanced SQL grouping sets, rollup, and cube patterns let you compute multiple aggregation levels in a single query, eliminating the need for separate GROUP BY statements while enabling the database optimizer to share scans and sort operations for massive performance gains.

The DataExpert-io/data-engineer-handbook repository demonstrates these enterprise-grade aggregation techniques through practical implementations in PostgreSQL. These patterns allow data engineers to generate subtotals, grand totals, and cross-dimensional summaries without resorting to multiple queries or complex UNION ALL constructs.

Understanding GROUPING SETS for Custom Aggregations

GROUPING SETS allow you to explicitly define multiple group-by combinations within a single query. Each set in the list produces separate result rows with appropriate subtotals, while the query engine shares work between sets to optimize performance.

How GROUPING SETS Work Internally

When you specify GROUP BY GROUPING SETS ((col1, col2), (col1), ()), the database engine executes the aggregation for each set but shares underlying table scans and sort operations. This is significantly more efficient than issuing several independent queries because the optimizer eliminates redundant I/O operations.

The GROUPING() function is essential for interpreting results. It returns 0 when a column participates in the current grouping set and 1 when it represents a super-aggregate (grand total) for that column.

Repository Implementation: Events Analysis

The file grouping_sets.sql in the repository's analytical patterns section demonstrates a real-world implementation using device and event data:

WITH events_augmented AS (
    SELECT COALESCE(d.os_type,      'unknown') AS os_type,
           COALESCE(d.device_type,  'unknown') AS device_type,
           COALESCE(d.browser_type, 'unknown') AS browser_type,
           url,
           user_id
    FROM events e
    JOIN devices d ON e.device_id = d.device_id
)
SELECT
    CASE
        WHEN GROUPING(os_type) = 0
         AND GROUPING(device_type) = 0
         AND GROUPING(browser_type) = 0 THEN 'os_type__device_type__browser'
        WHEN GROUPING(browser_type) = 0 THEN 'browser_type'
        WHEN GROUPING(device_type) = 0 THEN 'device_type'
        WHEN GROUPING(os_type) = 0 THEN 'os_type'
    END AS aggregation_level,
    COALESCE(os_type,      '(overall)') AS os_type,
    COALESCE(device_type,  '(overall)') AS device_type,
    COALESCE(browser_type, '(overall)') AS browser_type,
    COUNT(1) AS number_of_hits
FROM events_augmented
GROUP BY GROUPING SETS (
    (browser_type, device_type, os_type),
    (browser_type),
    (os_type),
    (device_type)
)
ORDER BY COUNT(1) DESC;

This query generates custom subtotals for browser-device-OS combinations, individual browsers, operating systems, and device types—all in one execution pass.

Leveraging ROLLUP for Hierarchical Subtotals

ROLLUP generates a hierarchy of subtotals by progressively removing columns from the right side of the grouping list. It is ideal for natural hierarchies like country → state → city or year → quarter → month.

ROLLUP Syntax and Behavior

The syntax GROUP BY ROLLUP (col1, col2, col3) is equivalent to specifying grouping sets of (col1, col2, col3), (col1, col2), (col1), and (). This creates a "drill-up" report showing detail rows followed by subtotals at each level and finally the grand total.

Player Performance Analysis Example

Using the schema defined in intermediate-bootcamp/materials/2-fact-data-modeling/tables/game_details.sql, you can analyze player statistics across seasons and teams:

SELECT player_id,
       season,
       team_id,
       SUM(points) AS total_points
FROM game_details
GROUP BY ROLLUP (player_id, season, team_id);

This query returns:

• Individual player-season-team totals
• Subtotals for each player-season across all teams
• Subtotals for each player across all seasons and teams
• The grand total across all players

Using CUBE for Cross-Dimensional Analysis

CUBE produces the Cartesian product of all possible subtotals for the supplied columns. Unlike ROLLUP, which follows a hierarchy, CUBE generates every possible combination of grouping and non-grouping for the given dimensions.

When to Choose CUBE Over ROLLUP

Use CUBE when you need to analyze data across multiple independent dimensions where every intersection matters. For example, analyzing sales by product, region, and quarter requires seeing totals for product-region, product-quarter, region-quarter, and each individual dimension—combinations that ROLLUP cannot produce because it only removes trailing columns.

Multi-Dimensional Aggregation Query

SELECT player_id,
       season,
       team_id,
       SUM(points) AS total_points
FROM game_details
GROUP BY CUBE (player_id, season, team_id);

This generates all eight possible aggregation levels, including combinations like season-team totals without player breakdown (which ROLLUP would not produce).

Combining Aggregation Patterns

You can mix GROUPING SETS, ROLLUP, and CUBE in a single query to create precisely tailored reports. This flexibility allows you to include hierarchical rollups alongside specific ad-hoc groupings.

Mixed ROLLUP and GROUPING SETS

The following pattern, referenced in intermediate-bootcamp/materials/4-applying-analytical-patterns/homework/homework.md, demonstrates combining hierarchical subtotals with separate grouping dimensions:

SELECT
    player_id,
    season,
    team_id,
    SUM(points) AS total_points
FROM game_details
GROUP BY
    ROLLUP (player_id, season),               -- hierarchical subtotals
    GROUPING SETS ((team_id), ());            -- separate subtotal for team and grand total

This hybrid approach generates subtotals for player-season hierarchies while independently grouping by team, enabling complex matrix-style reporting without multiple query passes.

Performance and Optimization Benefits

Using advanced SQL grouping sets and rollup/cube patterns delivers significant advantages over traditional aggregation methods:

• Shared Execution Plans: The optimizer performs a single scan of the underlying data, computing all aggregation levels simultaneously rather than reading the table multiple times.
• Reduced Network Overhead: One round-trip to the database replaces multiple queries, minimizing latency and connection overhead.
• Consistent Result Schema: All aggregation levels return in a uniform column structure, simplifying application logic that processes the results.

Summary

• GROUPING SETS explicitly define custom aggregation combinations, allowing non-hierarchical subtotal structures with optimal performance through shared scans.
• ROLLUP generates hierarchical subtotals by progressively removing right-most columns, perfect for drill-up reporting on natural hierarchies.
• CUBE produces the Cartesian product of all dimensional combinations, enabling complete cross-dimensional analysis across independent categories.
• The GROUPING() function identifies which aggregation level each row represents, essential for labeling and filtering results.
• These patterns are implemented in the DataExpert-io/data-engineer-handbook repository within intermediate-bootcamp/materials/4-applying-analytical-patterns/, demonstrating production-ready SQL for data engineering workflows.

Frequently Asked Questions

What is the difference between GROUPING SETS and ROLLUP?

GROUPING SETS requires you to explicitly list every column combination you want aggregated, offering complete control over which subtotals appear. ROLLUP is a shorthand that automatically generates a hierarchy of subtotals by successively removing the right-most column, producing (col1, col2), (col1), and grand total combinations without manual enumeration.

How does the GROUPING() function work in SQL?

The GROUPING() function returns 0 when the specified column is included in the current grouping set, and 1 when the column represents a super-aggregate (meaning it is aggregated across all values for that column). In the repository's grouping_sets.sql, this function powers the CASE statement that labels each row with its aggregation_level, making it possible to distinguish between detailed rows and various subtotal levels in the result set.

When should I use CUBE instead of ROLLUP?

Use CUBE when you need every possible combination of subtotals across multiple dimensions, such as analyzing intersections between product, region, and time period independently. Use ROLLUP when your dimensions form a natural hierarchy (like country to state to city) and you only need subtotals that roll up from detailed to summary levels, not cross-dimensional combinations like state-to-product totals.

Do all SQL databases support grouping sets, rollup, and cube?

Most modern enterprise databases support these features, including PostgreSQL 9.5+, SQL Server 2008+, Oracle 8i+, and Google BigQuery. MySQL added support for GROUPING SETS, ROLLUP, and CUBE in version 8.0. However, syntax nuances and optimizer capabilities vary by platform, so consult your specific database documentation when implementing these patterns in production environments.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}