GROUPBY,ROLLUP AND CUBE In SQL Server

     Here I am discussing about ROLLUP and CUBE in SQL Server. Both functions are used for aggregation. So we can check this with an example.

DECLARE @TransLog TABLE
(
ID INT PRIMARY KEY IDENTITY(1, 1),
ItemName VARCHAR(50) NOT NULL,
CompanyName VARCHAR(50) NOT NULL,
Amount MONEY NOT NULL
)


INSERT INTO @TransLog
SELECT 'Mobile','Walmart',5000
UNION ALL
SELECT 'Mobile','Amazon',6000
UNION ALL
SELECT 'Mobile','Amazon',4000
UNION ALL
SELECT 'Mobile','FlipKart',6000
UNION ALL
SELECT 'TV','Walmart',3000
UNION ALL
SELECT 'TV','Amazon',2000
UNION ALL
SELECT 'TV','FlipKart',8000
UNION ALL
SELECT 'TV','FlipKart',6000


SELECT * FROM @TransLog




     The above table shows Different product and amount received for each product. If we want to calculate total amount received for each product we can use GROUPBY in SQL Server.

SELECT
ItemName, SUM(Amount) AS TotalAmount
FROM @TransLog
GROUP BY ItemName






Next we want item wise total and grand total of whole product , here we are applying ROLLUP

SELECT
COALESCE (ItemName, 'Grand Total') AS ItemName,
SUM(Amount) AS TotalAmount
FROM @TransLog
GROUP BY ROLLUP (ItemName)




     We got item wise total and grand total using ROLLUP .Then Next I want all company wise total and Grand total using CUBE

SELECT
COALESCE (ItemName,'All TransType') AS ItemName,
COALESCE (CompanyName, 'All Company') AS Company,
SUM(Amount) AS Total
FROM @TransLog
GROUP BY CUBE (CompanyName, ItemName)











No comments:

Post a Comment