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 an 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 caluse 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 quantites for each region as well as returns the grand total of all purchased quantites 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.
Recommended for you
2,574 total views, 2 views today