Wednesday, April 25, 2018

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



4 comments:

  1. Hello,
    Thank you Microsoft Business Intelligence for the post update regularly. KernelTraining is one of the leading DevOps training institutes. Recently i completed my software course in KernelTraining, good Institute where you can get good knowledge by real-time experts. I would strongly recommend KernelTraining. for more details please go through the link: DevOps Training In Hyderabad

    ReplyDelete
  2. Thank you for this wonderful post, great article, keep up the excellent work.
    Java training institute in Hyderabad

    ReplyDelete