Advanced Grouping in SQL - GROUPING SETS, CUBE, and ROLLUP

Introduction

Advanced grouping techniques let you create multiple grouping combinations in a single query. Think of it as getting subtotals and totals at different levels all at once.

Why Advanced Grouping Matters?

It’s useful when you need to:

• Create summary reports
• Show multiple aggregation levels
• Generate subtotals and totals
• Analyze data at different levels

Main Content: Understanding Advanced Grouping

Let’s use this simple table:

-- Create a simple table
CREATE TABLE sales (
    region VARCHAR(50),
    product VARCHAR(50),
    year INT,
    amount DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO sales VALUES
('North', 'Laptop', 2023, 1000),
('North', 'Phone', 2023, 500),
('South', 'Laptop', 2023, 1200),
('South', 'Phone', 2023, 600),
('North', 'Laptop', 2024, 1100),
('North', 'Phone', 2024, 550),
('South', 'Laptop', 2024, 1300),
('South', 'Phone', 2024, 650);

Example 1: GROUPING SETS

Let’s analyze sales at different levels:

SELECT 
    COALESCE(region, 'All Regions') as region,
    COALESCE(product, 'All Products') as product,
    SUM(amount) as total_sales
FROM sales
GROUP BY GROUPING SETS (
    (region, product),
    (region),
    (product),
    ()
)
ORDER BY region, product;

This query shows:

1. Sales by region and product
2. Sales by region only
3. Sales by product only
4. Total sales

Results:

region       | product      | total_sales
-------------|-------------|-------------
All Regions  | All Products| 6900.00
All Regions  | Laptop      | 4600.00
All Regions  | Phone       | 2300.00
North        | All Products| 3150.00
North        | Laptop      | 2100.00
North        | Phone       | 1050.00
South        | All Products| 3750.00
South        | Laptop      | 2500.00
South        | Phone       | 1250.00

Example 2: ROLLUP

Let’s create hierarchical summaries:

SELECT 
    COALESCE(region, 'All Regions') as region,
    COALESCE(year::TEXT, 'All Years') as year,
    SUM(amount) as total_sales
FROM sales
GROUP BY ROLLUP(region, year)
ORDER BY region, year;

This query creates:

1. Region and year combinations
2. Region subtotals
3. Grand total

Results:

region       | year      | total_sales
-------------|-----------|-------------
All Regions  | All Years | 6900.00
North        | 2023      | 1500.00
North        | 2024      | 1650.00
North        | All Years | 3150.00
South        | 2023      | 1800.00
South        | 2024      | 1950.00
South        | All Years | 3750.00

Example 3: CUBE

Let’s see all possible combinations:

SELECT 
    COALESCE(region, 'All Regions') as region,
    COALESCE(product, 'All Products') as product,
    COALESCE(year::TEXT, 'All Years') as year,
    SUM(amount) as total_sales
FROM sales
GROUP BY CUBE(region, product, year)
ORDER BY region, product, year;

This query shows:

1. All possible grouping combinations
2. Subtotals at every level
3. Grand total

Results (partial):

region       | product      | year      | total_sales
-------------|-------------|-----------|-------------
All Regions  | All Products| All Years | 6900.00
All Regions  | All Products| 2023      | 3300.00
All Regions  | All Products| 2024      | 3600.00
All Regions  | Laptop      | All Years | 4600.00
All Regions  | Phone       | All Years | 2300.00
North        | All Products| All Years | 3150.00
North        | Laptop      | 2023      | 1000.00
...

Best Practices

1. Choosing the Right Technique

   • GROUPING SETS: Specific combinations
   • ROLLUP: Hierarchical summaries
   • CUBE: All possible combinations

2. Result Readability

   • Use COALESCE for NULL values
   • Add meaningful labels
   • Order results logically
   • Format numbers appropriately

3. Performance Considerations

   • Consider data volume
   • Limit number of dimensions
   • Index grouped columns
   • Test with real data

4. Common Uses

   • Sales reports
   • Performance analysis
   • Multi-level summaries
   • Trend analysis

5. Things to Remember

   • CUBE generates most combinations
   • ROLLUP follows hierarchy
   • GROUPING SETS is most specific
   • NULL handling is important

Remember:

• GROUPING SETS: Custom combinations
• ROLLUP: Hierarchical totals
• CUBE: All combinations
• Handle NULLs appropriately

Common Mistakes to Avoid:

1. Too many dimensions
2. Missing NULL handling
3. Wrong grouping choice
4. Poor result formatting

Happy querying! 🚀