GROUPING SETS clause in SQL Server

 SQL GROUPING SETS allows computing multiple GROUP BY clauses in a single statement. The results of GROUPING SETS are the equivalent of UNION ALL of the specified groups.

Table

create table emp   (      empid      INT primary key,      emp_name   VARCHAR(50),      dept_name  VARCHAR(50),      office_add VARCHAR(50),      salary     DECIMAL(18, 10)   ) insert into emp(empid,emp_name,dept_name,office_add,salary) values(1,'Bagesh','IT','Noida',25000); insert into emp(empid,emp_name,dept_name,office_add,salary) values(2,'Rajesh','IT','Pune',28000); insert into emp(empid,emp_name,dept_name,office_add,salary) values(3,'Amit','Finance','Noida',45000); insert into emp(empid,emp_name,dept_name,office_add,salary) values(4,'Raju','HR','Pune',30000); insert into emp(empid,emp_name,dept_name,office_add,salary) values(5,'Mahesh','HR','Noida',28000); insert into emp(empid,emp_name,dept_name,office_add,salary) values(6,'Mukesh','Finance','Pune',28000);

 

We want to sum of all combination. Like group by emp_name,dept and office_add , dept_name,Office_add and office_add and dept_name and finally total of all. See as below.

We need to write the below query.

select emp_name,        dept_name,        office_add,        Sum(salary) as salary from   emp group  by emp_name,           dept_name,           office_add union all select null,        dept_name,        office_add,        Sum(salary) as salary from   emp group  by dept_name,           office_add union all select null,        null,        dept_name,        Sum(salary) as salary from   emp group  by dept_name union all select null,        null,        office_add,        Sum(salary) as salary from   emp group  by office_add union all select null,        null,        null,        Sum(salary) as salary from   emp

  

In place of writing complex and big query to get this result we can use Grouping Set clause. See the syntax below.

Select C1,C2,C3, Aggregate function (C4) From Table where Filter condition (optional of we are filtering some data)  Group by GROUPING SET ( (C1,C2,C3), --Grouping set 1 (C2,C3),-- Grouping set 2 (C1)—Grouping set 3 (C2),--Grouping set 4 (C3),--Grouping set 5 () -- Grand Total )

See the example

select emp_name,        dept_name,        office_add,        Sum(salary) as salary from   emp group  by grouping sets ( ( emp_name, dept_name, office_add ),             -- Sum of Salary by emp_name,dept_name,office_add)             ( dept_name, office_add ), -- Sum of Salary by dept_name,office_add             ( office_add ), -- Sum of Salary by office_add             ( dept_name ), -- Sum of Salary by ept_name             ( )-- Grand Total            )

Hence we can say that GROUPING SET is able to generate a result set that can be generated by a UNION ALL of multiple simple GROUP BY clauses. It is capable of generating a result set that is equivalent to the result set generated by ROLL UP or CUBE operations.

See below

Grouping sets equivalent of UNION ALL
Select c1,c2,sum(c3) from table group by grouping set ((c1,c2), (c1),(c2),())	Select c1,c2,sum(c3) from table group by c1,c2 Union all Select c1,null,sum(c3) from table group by c1 Union all Select null,c2,sum(c3) from table group by c2 Union al Select null,null, sum(c3) from table
Grouping sets Rollup equivalents
Select c1,c2,c3,sum(c4) from table Group by rollup(c1,c2,c3)	Select c1,c2,c3,sum(c4) from table Group by grouping sets((c1,c2,c3), (c1,c2), (c1),())
Grouping SETS CUBE Equivalents
Select c1,c2,c3,sum(c4) from table Group by cube(c1,c2,c3)	Select c1,c2,c3,sum(c4) from table Group by grouping sets((c1,c2,c3), (c1,c2), (c1,c3), (c2,c3), (c1), (c2), (c3),())