TSQL - GROUP BY with ROLLUP and CUBE

The ROLLUP operator is a very useful in generating reports that contain subtotals and totals which generates a result set that shows aggregates for a hierarchy of values in the selected columns. The super-aggregated column is represented by a NULL value and we can assign any value such as ‘ALL’, although the super-aggregate rows will always be added last. Multiple aggregates over different columns will be added if there are multiple GROUP BY columns.
It is useful- 
A. By using the ROLLUP option, you can use a single query to generate multiple grouping sets.
B. ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.
C. By using ROLLUP, you can find total for corresponding to one or more columns

Examples
1. SQL ROLLUP with single column
2. SQL ROLLUP with multiple columns

3. SQL ROLLUP with partial rollup

WITH ROLLUP, as well as WITH CUBE, both are non-standard and deprecated.

To understand both operators, we will observe both the ROLLUP and CUBE operators with the help of a simple example as given below-

----- Create table variable DECLARE @Item_List Table (Product Varchar(20), Maker Varchar(20), Quantity Int) ----- Insert Data into table variable INSERT INTO @Item_List(Product,Maker, Quantity ) VALUES ('Laptop', 'Dell', 300), ('Laptop', 'Lenovo', 150), ('Laptop', 'HP', 400), ('Desktop', 'Dell', 120), ('Desktop', 'Lenovo', 80), ('Desktop', 'HP', 250) ----- Pull the data from table variable SELECT * FROM @Item_List; Product Maker Quantity Desktop Dell 120 Desktop HP 250 Desktop Lenovo 80 Laptop Dell 300 Laptop HP 400 Laptop Lenovo 150 ----- Result by using WITH ROLLUP; SELECT Product=ISNULL(Product,'ALL'), Maker=ISNULL(Maker,'ALL'), Quantity=Sum(Quantity) FROM @Item_List GROUP BY Product, Maker WITH ROLLUP; Product Maker Quantity Desktop Dell 120 Desktop HP 250 Desktop Lenovo 80 Desktop ALL 450 Laptop Dell 300 Laptop HP 400 Laptop Lenovo 150 Laptop ALL 850 ALL ALL 1300

In addition to the subtotals generated by the ROLLUP extension, the WITH CUBE operator will generate subtotals for all combinations of the dimensions specified. In general, WITH CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.

---- Result by using With Cube SELECT Product=ISNULL(Product,'ALL'), Maker=ISNULL(Maker,'ALL'), Quantity=Sum(Quantity) FROM @Item_List GROUP BY Product , Maker WITH Cube ; Product Maker Quantity Desktop Dell 120 Laptop Dell 300 ALL Dell 420 Desktop HP 250 Laptop HP 400 ALL HP 650 Desktop Lenovo 80 Laptop Lenovo 150 ALL Lenovo 230 ALL ALL 1300 Desktop ALL 450 Laptop ALL 850

As the number of dimensions/columns increase, so do the combinations of subtotals that need to be calculated. The ROLLUP operator can also be used to calculate sub-totals for each column, based on the groupings within that column.

The Difference between ROLLUP and CUBE

There is only one major difference between the functionality of the ROLLUP operator and the CUBE operator. ROLLUP operator generates aggregated results for the selected columns in a hierarchical way whereas CUBE generates an aggregated result that contains all the possible combinations for the selected columns or dimensions.

To Learn more, please visit our YouTube channel at - 

http://www.youtube.com/c/Sql-datatools