RollUp ,CUBE Operator

CREATE TABLE InventoryInfo
(
SL_No INT IDENTITY PRIMARY KEY,
InventoryMasterName char(50),
StateId CHAR(10) FOREIGN KEY REFERENCES StateInfo(StateId),
InventoryDate DATE,
InventoryAmount MONEY
)

INSERT INTO InventoryInfo VALUES
('Nisha Singh' , 'R0001' , '01/02/2015',5000),

('Nisha Singh' , 'R0001' , '02/02/2015',3500),

('Nisha Singh' , 'R0001' , '03/02/2015',4000),

('Prince' , 'R0001' , '01/02/2015',5340),

('Prince' , 'R0001' , '02/02/2015',4300),

('Prince' , 'R0001' , '03/02/2015',5000),

('Prince' , 'R0002' , '01/02/2015',5980),

('Prince' , 'R0003' , '01/02/2015',8790),

('Prince' , 'R0004' , '01/02/2015',5000),

('Nisha Singh' , 'R0002' , '01/02/2015',9800),

('Nisha Singh' , 'R0003' , '01/02/2015',8760),

('Nisha Singh' , 'R0004' , '01/02/2015',34230),

('Nisha Singh' , 'R0004' , '02/02/2015',15000)




--Using RollUp Operator



SELECT InventoryMasterName , StateId , grouping_id( InventoryMasterName , StateId ) as 'g n',SUM(InventoryAmount) FROM InventoryInfo
GROUP BY ROLLUP(InventoryMasterName , StateId)

SELECT InventoryMasterName , StateId , InventoryDate,SUM(InventoryAmount) FROM InventoryInfo
GROUP BY ROLLUP(InventoryMasterName , StateId , InventoryDate)

--Using CUBE Operator


SELECT InventoryMasterName , StateId , grouping_id( InventoryMasterName , StateId ) as 'g n',SUM(InventoryAmount) FROM InventoryInfo
GROUP BY CUBE(InventoryMasterName , StateId)

SELECT InventoryMasterName , StateId , SUM(InventoryAmount) FROM InventoryInfo
GROUP BY CUBE(InventoryMasterName , StateId , InventoryDate)

SELECT InventoryMasterName , StateId ,InventoryDate,grouping_id( InventoryMasterName , StateId , InventoryDate) as 'group number', SUM(InventoryAmount) FROM InventoryInfo
GROUP BY CUBE(InventoryMasterName , StateId , InventoryDate)

order by 'group number',InventoryMasterName , StateId , InventoryDate