CUBE Operator in SQL Server

CUBE operator produce subtotal and grandtotal for every permutation of the columns provided to it. We will use the given table to understand it with an example

CREATE TABLE Stock
(
ProductName NVARCHAR(20),
Model NVARCHAR(30),
Quantity INT,
Godown NVARCHAR(50)
)

GO

INSERT INTO Stock(ProductName, Model, Quantity, Godown)
SELECT 'Samsung','Galaxy M31',50,'Delhi' UNION
SELECT 'Samsung','Galaxy F31',50,'Delhi' UNION
SELECT 'iPhone','12 Pro',25,'Delhi' UNION
SELECT 'iPhone','5S',25,'Delhi' UNION
SELECT 'Samsung','Galaxy M31',50,'UP' UNION
SELECT 'Samsung','Galaxy F12',150,'UP' UNION
SELECT 'iPhone','5S',65,'UP' UNION
SELECT 'iPhone','4S',75,'UP' UNION
SELECT 'Samsung','Galaxy M31',37,'HR' UNION
SELECT 'Samsung','Galaxy F12',97,'HR' UNION
SELECT 'iPhone','5S',38,'HR' UNION
SELECT 'iPhone','4S',150,'HR' 

Result:-

Question 1:- Suppose we want to create a report which contains given permutation
(I) ProductName and Godown wise Total Quantity
(II) Godown wise subtotal
(III) Grand total
(IV) Product wise total quantity

1. We can use CUBE to produce data as per above image

SELECT ProductName,Godown,SUM(Quantity) Quantity 
FROM Stock
GROUP BY ProductName,Godown WITH CUBE

2. We can also use GROUPING SETS which produce the same output and equivalent to above CUBE query

SELECT ProductName,Godown,SUM(Quantity) Quantity 
FROM Stock
GROUP BY GROUPING SETS 
(
	(ProductName,Godown), --This will produce ProductName and Godown wise Total Quantity
	(Godown), -- This will show Godown wise subtotal
	(ProductName), -- This will show Product wise total quantity	
	()	--Grand total
)

3. We can also produce the same output using UNION ALL which is equivalent to above GROUPING SETS query

SELECT ProductName,Godown,SUM(Quantity) Quantity 
FROM Stock 
GROUP BY ProductName,Godown

UNION ALL

SELECT NULL,Godown,SUM(Quantity) Quantity 
FROM Stock 
GROUP BY Godown

UNION ALL

SELECT ProductName,NULL,SUM(Quantity) Quantity 
FROM Stock 
GROUP BY ProductName

UNION ALL

SELECT NULL,NULL,SUM(Quantity) Quantity 
FROM Stock 

Note: order of this query may be differt. you can use Order By clause to correct the order.