Rollup, Cube and Grouping Sets in SQL | Advanced SQL Tutorial | Beyond Group By

Introduction

This tutorial will guide you through advanced SQL concepts including Rollup, Cube, and Grouping Sets. These powerful tools enhance your data aggregation capabilities beyond the standard Group By, allowing for more detailed and flexible reporting. Understanding these features will help you generate comprehensive reports from your SQL queries.

Step 1: Create the Orders Table

To begin using Rollup, Cube, and Grouping Sets, you first need a dataset. For this, we will create an orders table.

1. Open your SQL environment.
2. Run the following SQL commands to create the table and insert sample data:

CREATE TABLE orders (
    Id INT PRIMARY KEY IDENTITY,
    Continent NVARCHAR(50),
    Country NVARCHAR(50),
    City NVARCHAR(50),
    Amount INT
);

INSERT INTO orders VALUES ('Asia', 'India', 'Bangalore', 1000);
INSERT INTO orders VALUES ('Asia', 'India', 'Chennai', 2000);
INSERT INTO orders VALUES ('Asia', 'Japan', 'Tokyo', 4000);
INSERT INTO orders VALUES ('Asia', 'Japan', 'Hiroshima', 5000);
INSERT INTO orders VALUES ('Europe', 'United Kingdom', 'London', 1000);
INSERT INTO orders VALUES ('Europe', 'United Kingdom', 'Manchester', 2000);
INSERT INTO orders VALUES ('Europe', 'France', 'Paris', 4000);
INSERT INTO orders VALUES ('Europe', 'France', 'Cannes', 5000);

Step 2: Use Group By for Basic Aggregation

Start with the standard Group By to see how data aggregation works.

1. Run the following SQL query to group by continent and sum the amounts:

SELECT Continent, SUM(Amount) AS TotalAmount 
FROM orders 
GROUP BY Continent;

2. This will return the total sales by continent.

Step 3: Aggregate with Rollup

Now, use Rollup to get subtotals at different levels of aggregation.

1. Modify your query to include Rollup:

SELECT Continent, Country, City, SUM(Amount) AS TotalAmount 
FROM orders 
GROUP BY ROLLUP (Continent, Country, City);

2. This will provide totals at the city level, country level, continent level, and an overall total.

Common Pitfall

Rollup only aggregates in a hierarchical manner (from left to right), meaning some combinations might be missing. For example, it won't show totals for just Country and City.

Step 4: Aggregate with Cube

To retrieve all possible combinations, use Cube.

1. Replace Rollup with Cube in your query:

SELECT Continent, Country, City, SUM(Amount) AS TotalAmount 
FROM orders 
GROUP BY CUBE (Continent, Country, City);

2. With Cube, you will get all combinations of the specified columns (e.g., totals for just Country, just City, etc.).

Step 5: Use Grouping Sets for Custom Aggregation

Grouping Sets allow you to specify exactly which aggregations you want.

1. Use the following query to define specific sets:

SELECT Continent, Country, City, SUM(Amount) AS TotalAmount 
FROM orders 
GROUP BY GROUPING SETS (
    (Continent, Country),
    (City),
    (Continent, City)
);

2. This will return results only for the specified combinations.

Practical Tips

• Use Rollup for a quick summary when you need hierarchical totals.
• Choose Cube for comprehensive analysis when you need all combinations.
• Opt for Grouping Sets when you want precise control over the aggregations.

Conclusion

In this tutorial, you learned how to utilize Rollup, Cube, and Grouping Sets in SQL for advanced data aggregation. These tools can significantly enhance your reporting capabilities. As a next step, practice these concepts with different datasets to solidify your understanding and improve your SQL skills.