September 07, 2009

ROLLUP vs CUBE

ROLLUP vs CUBE in SQL Server

Understanding SQL ROLLUP and CUBE

SQL Server allows us to use ROLLUP and CUBE for hierarchical and multidimensional aggregations.

ROLLUP:

Generates subtotals and a grand total for a hierarchy of columns.
Useful for generating reports with hierarchical data.
Can be used with GROUP BY to create subtotals.
Example: If grouping by Year, Month, and Day, ROLLUP will produce:

Year, Month, Day
Year, Month
Year
Grand Total

CUBE:

Generates subtotals for all possible combinations of columns.
Useful for multidimensional analysis.
Can be used with GROUP BY to create subtotals for all combinations.
Example: If grouping by Year, Month, and Day, CUBE will produce:

Year, Month, Day
Year, Month
Year, Day
Year
Month, Day
Month
Day
Grand Total

Sample Table and Data


CREATE TABLE Sales (
    ID INT,
    FName VARCHAR(30),
    Zone VARCHAR(30),
    Sale INT
);

INSERT INTO Sales (ID, FName, Zone, Sale)
VALUES
    (1, 'Mangal', 'East', 20),
    (2, 'Mangal', 'East', 150),
    (3, 'Mangal', 'West', 50),
    (4, 'Ram', 'East', 45),
    (5, 'Ram', NULL, 80),
    (6, 'Ram', NULL, 40),
    (7, 'Sachin', 'West', 50),
    (8, 'Sachin', 'West', 40);

Sample Data:

ID	FName	Zone	Sale
1	Mangal	East	20
2	Mangal	East	150
3	Mangal	West	50
4	Ram	East	45
5	Ram	Unknown	80
6	Ram	Unknown	40
7	Sachin	West	50
8	Sachin	West	40

SQL ROLLUP Query


SELECT 
    CASE GROUPING(FName) 
        WHEN 1 THEN 'All Names' 
        ELSE ISNULL(FName, 'Unknown') 
    END AS FName,
    CASE GROUPING(Zone) 
        WHEN 1 THEN 'All Zones' 
        ELSE ISNULL(Zone, 'Unknown') 
    END AS Zone,
    SUM(Sale) AS Total
FROM Sales
GROUP BY FName, Zone WITH ROLLUP
ORDER BY GROUPING(FName), FName, GROUPING(Zone), Zone;

ROLLUP Output:

FName	Zone	Total
Mangal	East	170
Mangal	West	50
Mangal	All Zones	220
Ram	East	45
Ram	Unknown	120
Ram	All Zones	165
Sachin	West	90
Sachin	All Zones	90
All Names	All Zones	475

Explanation:

The ROLLUP query generates subtotals for each name and zone, as well as a grand total for all names and zones.

SQL CUBE Query


SELECT 
    CASE GROUPING(FName) 
        WHEN 1 THEN 'All Names' 
        ELSE ISNULL(FName, 'Unknown')
    END AS FName,
    CASE GROUPING(Zone) 
        WHEN 1 THEN 'All Zones' 
        ELSE ISNULL(Zone, 'Unknown') 
    END AS Zone,
    SUM(Sale) AS Total
FROM Sales
GROUP BY FName, Zone WITH CUBE
ORDER BY GROUPING(FName), FName, GROUPING(Zone), Zone;

CUBE Output:

FName	Zone	Total
Mangal	East	170
Mangal	West	50
Mangal	All Zones	220
Ram	East	45
Ram	Unknown	120
Ram	All Zones	165
Sachin	West	90
Sachin	All Zones	90
All Names	East	215
All Names	West	140
All Names	Unknown	120
All Names	All Zones	475

Explanation:

The CUBE query generates subtotals for all combinations of names and zones, as well as a grand total for all names and zones.

Compute:

Compute is a SQL Server feature that allows you to calculate running totals and other aggregations on the result set. It can be used with ROLLUP and CUBE to provide additional insights into your data.

SQL CUBE Query


        SELECT ProductID, OrderID, Quantity
        FROM [Order Details]
        ORDER BY ProductID, OrderID
        COMPUTE SUM(Quantity);

Conclusion

ROLLUP is used for hierarchical aggregations, while CUBE is used for multidimensional aggregations. Choose the one that best fits your reporting needs.

February 15, 2009

Group by vs Grouping Set

Deterministic vs Nondeterministic Functions in SQL

Understanding Grouping Sets in SQL

To know the proper use of "Grouping Set," we should first understand the use of "Group By."

Normally, "Group By" is used to group similar kinds of data and display unique records. Aggregate functions can also be applied to calculate data. For example, we have different models of cars and trucks along with their quantities. Let’s take a look at the data in the table below:

Now we can add (sum) the number of vehicles based on their color, category, type, or all of these together. The SQL "Group By" command can group values by the selected columns.

For instance:

Why Use Grouping Sets?

What if we need to see totals for each individual key performance indicator (KPI) and the overall totals for all KPIs? In this case, we use "Grouping Sets" in SQL Server—a very powerful feature.

In this query, we calculate the total number of vehicles based on their category, type, and color, as well as combinations of these attributes.

Grouping Sets allow us to aggregate data on multiple levels, whereas "Group By" only works with all specified fields in the query.