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.
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.
Next we want item wise total and grand total of whole product , here we are applying ROLLUP
We got item wise total and grand total using ROLLUP .Then Next I want all company wise total and Grand total using CUBE
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)
