SQL SERVER ROLLUP

SQL SERVER ROLLUP clause is used to display subtotals and grand totals in the result set. It is always used with GROUP BY CLAUSE.

Group by can not aggregate data on multiple levels of a hierarchy’s like to display sub total (one level of hierarchy) and grand total (two levels of hierarchy) in the result set.

For example you can calculate the total of all purchased for each Item (one level of hierarchy) but cannot calculate the total purchased of all Item regardless of what item it is (two levels of hierarchy).

In such case, you can use ROLLUP clause as it extend the functionality of GROUP BY CLAUSE  by calculating subtotals and grand totals for a set of columns . 

SYNTAX

SELECT
column1, column2,.., aggregate_function(columnname)
FROM table_name 
GROUP BY ROLLUP(column1, Column2,...,)

Lets look at an example of ROLLUP in SQL.

To demonstrate this, first we create a sample table named ItemQty which contains the details of purchased Item and insert some records in table.

CREATE TABLE dbo.ItemQty
(
RowId INT IDENTITY(1,1),
Region VARCHAR(50),
Item VARCHAR(50),
Year INT,
Quantity INT
)
INSERT INTO dbo.ItemQty(Region, Item, Year, Quantity)
VALUES('North', 'Computer', 2019, 1300),
('South', 'Computer', 2019, 4500),
('East', 'Computer', 2019, 2500),
('North', 'Hard Disk', 2019, 900),
('West', 'Computer', 2019, 2500),
('South', 'Hard Disk', 2019, 3500),
('West', 'Hard Disk', 2019, 4500),
('North', 'Pen Drive', 2020, 1500),
('West', 'Pen Drive', 2020, 2600),
('South', 'Pen Drive', 2020, 2700),
('East', 'Pen Drive', 2020, 2100),
('East', 'Pen Drive', 2019, 1800),
('West', 'Pen Drive', 2019, 1000),
('North', 'Pen Drive', 2019, 900)

As you can see now we have a table named ItemQty as shown below.

SELECT * FROM dbo.ItemQty

Lets fetch the Total purchased Quantity of item’s by Region.

SELECT Region, SUM(Quantity) AS TotalQty
FROM dbo.ItemQty 
GROUP BY Region

As you can see above result, to get the total purchased Quanity by Region, Group By statement is the better option.

But just in case, when you want to see the Grand total of all quantities also in result, that simply can not be returned by using GROUP BY statement as Group By statement can aggregate the data at one level that is subtotal not grand total.

For such case when you want to see the Sub total as well as Grand total for category, you can use ROLLUP clause as it extends the functionality of GROUP By clause and returns the Grand total also.

Using ROLLUP to get the subtotal and grandtotal

Following statement uses the ROLLUP clause which returns the Subtotal of quantities also GrandTotal.

SELECT Region, SUM(Quantity) AS TotalQty
FROM dbo.ItemQty 
GROUP BY ROLLUP(Region)

As you can see, it returns the total purchased quantities for each region as well as returns the grand total of all purchased quantities by region. It adds an extra row in result set to display the GrandTotal.

You can see, for GrandTotal value there is null in place of Region name. You can also give it meaningful name by replacing null value with any specified text using COALESE funtion.

Lets replace the null value with “Grand Total”.

SELECT COALESCE(Region ,'GRAND TOTAL' ) AS Region, 
SUM(Quantity) AS TotalQty
FROM dbo.ItemQty 
GROUP BY ROLLUP(Region)

To display the total purchased quantities by region and items with SubTotal and GrandTotal 

SELECT COALESCE(Region,'GRAND TOTAL') AS Region,
COALESCE(Item ,'TOTAL') AS Item, SUM(Quantity) AS TotalQty
FROM dbo.ItemQty
GROUP BY ROLLUP(Region, Item)

As you can see, it returns the result with total purchased quantities by Region and Items with subtotal total as well as grand total.

To display the total purchased quantities by year, region and items with SubTotal and GrandTotal 

SELECT COALESCE(CAST(Year AS VARCHAR) ,'GRAND TOTAL') AS Year,
COALESCE(Region ,'SUB TOTAL') AS Region, 
COALESCE(Item ,'TOTAL') AS Item,
SUM(Quantity) AS TotalQty
FROM dbo.ItemQty
GROUP BY ROLLUP(CAST(Year AS VARCHAR), Region, Item)

As you can see, it returns Sub Total for each hierarchy level as well as Grand Total. You can give any name to Total, Grand Total, and Sub Total as per your requirement.

Also Read..

SQL Server Cube

SQL Server Grouping Sets