ROLLUP Operator

The ROLLUP operator is useful in generating reports that contain subtotals and totals. For example when we want the report which shows URL Count for each Category and Vendor , simply we can use group by Category and Vendor

SELECT CategoryName ,VENDOR , COUNT(URL) AS URLS
FROM testRoolUp
GROUP BY CategoryName , VENDOR
Output:

When we want report like below , Here ROLLUP and GROUPING comes into the picture and help us. ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.




Example
-- *********** ROLLUP ****************
--Create Table
CREATE TABLE testRoolUp
(
CategoryName VARCHAR(100),
Vendor VARCHAR(100),
URL NVARCHAR(MAX)
)
-- Insert data
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Amazon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Amazon', 'http://www.amazon.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Amazon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')

INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Amazon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Amazon', 'http://www.amazon.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Amazon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')

-- ROLLUP
SELECT CategoryName ,VENDOR , COUNT(URL) AS URLS
FROM testRoolUp
GROUP BY CategoryName , VENDOR
WITH ROLLUP



GROUPING clause add new column in result set. The value in new column can either be 0 or 1. If the new row is added by the ROLLUP then the value of GROUPING column is 1 else 0.

-- ROLLUP With GROUPING

SELECT
CASE WHEN GROUPING(CategoryName) = 1 THEN 'ALL Categories' ELSE CategoryName END AS Category_Name ,
CASE WHEN GROUPING(VENDOR) = 1 THEN 'ALL Vendors' ELSE VENDOR END AS Vendor, COUNT(URL) AS URLS
FROM testRoolUp
GROUP BY CategoryName , VENDOR
WITH ROLLUP