The ROLLUP operator used with GROUP BY Clause. It is useful in generating reports which contain subtotals and Grand Totals. It generates a result set that shows aggregates for a hierarchy of values based on the columns passed to it. Let's write a table and insert some data 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:- Write a query to calculate Godown wise Grand Total as per the below image.

We can do it in different ways-

(I). By writing two queries using SUM with GROUP BY clause then union the result set of both queries as given below

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

UNION ALL

SELECT NULL,SUM(Quantity) Quantity 
FROM Stock
(II). Using GROUPING SETS 
SELECT Godown,SUM(Quantity) Quantity 
FROM Stock
GROUP BY GROUPING SETS
(
	(Godown),
	()
)
(III). The third method is very simple just add WITH ROLLUP clause in the GROUP BY Clause and you will get the desired result set. 
SELECT Godown,SUM(Quantity) Quantity 
FROM Stock
GROUP BY Godown WITH ROLLUP
Question 2:- Write a query to calculate ProductName and Model wise total Quantity, ProductName wise  Subtotal and calculate Grand Total for all Products as given in the below image.


(I) Using UNION ALL with GROUP BY Clause 
SELECT ProductName,model,SUM(Quantity) Quantity 
FROM Stock
GROUP BY ProductName,model 

UNION ALL

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

UNION ALL

SELECT NULL,NULL,SUM(Quantity) Quantity 
FROM Stock
(II) Using GROUPING SETS 
SELECT ProductName,model,SUM(Quantity) Quantity 
FROM Stock
GROUP BY GROUPING SETS
( 
	(ProductName,model),
	(ProductName),
	()
)
(III) Using ROLLUP with GROUP BY Clause 
SELECT ProductName,model,SUM(Quantity) Quantity 
FROM Stock
GROUP BY ProductName,model WITH ROLLUP