GROUPING SETS is a subclause of the GROUP BY clause to specify multiple group-by clauses in a single query.
Let's create 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:-


Now Write a query to produce output as per the given image.



There are two approaches to produce data like this

1. Create multiple result set using group by clause and merge them using UNION ALL operator. 
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 NULL, NULL, SUM(Quantity) Quantity 
FROM Stock
2. By using the GROUPING SETS operator along with the GROUP BY clause. 
SELECT ProductName, Godown, SUM(Quantity) Quantity 
FROM Stock
GROUP BY 
GROUPING SETS
(
	(ProductName,Godown),
	(Godown),
	()
)
Output of the second approach does not match with output of the first approach. So to match the output of both approaches we have to use order by clause as given below 

SELECT ProductName, Godown, SUM(Quantity) Quantity 
FROM Stock
GROUP BY 
GROUPING SETS
(
	(ProductName,Godown),
	(Godown),
	()
)
ORDER BY GROUPING(ProductName),GROUPING(Godown)
UNION ALL vs GROUPING SETS

1. The query using UNION ALL is lengthy as we have merged multiple Group By queries using UNION ALL operator On the other hand GROUPING SETS reduces the number of lines and makes our code cleaner and readable.

2. There is a performance issue in combing result sets by UNION ALL as it has scanned stock table four times once for each query On the other hand GROUPING SETS scanned the stock table only once.


so it is always good to use GROUPING SETS in this type of scenario.

GROUPING SETS Equivalents