GROUPING SETS Clause in SQL Server

Introduction

We all know the use of a "group by" statement. Normally a GROUP BY statement is used in conjunction with aggregate functions (like sum, count, avg etc.) to group the result-set by one or more columns. In a sort, whenever data is to be summarized, the developer uses a Group By clause. The GROUP BY clause is also used to get DISTINCT rows from any result set.

GROUPING SET is introduced in SQL Server 2008. 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.

GROUPING SETS Equivalents

Specifying GROUPING SETS as the GROUP BY list is equivalent to a UNION ALL of queries, each with one of the grouping sets as its GROUP BY list. We can also use GROUPING SETS in replacement of ROLLUP and CUBE.

Example

In this example, an Employee Master table is created and some dummy values are inserted:

CREATE TABLE #EmployeeMaster
(
      EmployeeCode varchar(10),
      EmployeeName varchar(25),
      DepartmentCode varchar(10),
      LocationCode varchar(10),
      salary int
)

INSERT into #EmployeeMaster(EmployeeCode, EmployeeName, DepartmentCode, LocationCode ,salary)
VALUES
('E0001', 'Jignesh', 'IT','GNR', 2000),
('E0002', 'Tejas', 'IT','GNR',  5000),
('E0003', 'Rakesh', 'QA','BVN', 4000),
('E0004', 'Bhavin', 'QA','BVN', 2000),
('E0005', 'Sandip', 'HR','ABD', 3000),
('E0005', 'Tarun', 'HR','ABD', 5000)

Now I want some summarized data, like total cost by Employee, total cost by Department, total cost by location and total cost for all employees with all locations in a single result set.

Before SQL Server 2008, to do this we must write a different query and UNION these queries (this is the same as the following query).

SELECT EmployeeCode, DepartmentCode, LocationCode, SUM(salary) TotalCost
FROM #EmployeeMaster
Group BY EmployeeCode, DepartmentCode, LocationCode
UNION
SELECT NULL AS EmployeeCode, DepartmentCode, NULL AS LocationCode, SUM(salary) TotalCost
FROM #EmployeeMaster
Group BY DepartmentCode
UNION
SELECT NULL AS EmployeeCode, NULL AS DepartmentCode, LocationCode, SUM(salary) TotalCost
FROM #EmployeeMaster
Group BY LocationCode
UNION
SELECT NULL AS EmployeeCode, NULL AS DepartmentCode, NULL AS LocationCode, SUM(salary) TotalCost
FROM #EmployeeMaster

With SQL Server 2008:

SELECT EmployeeCode, DepartmentCode, LocationCode, SUM(salary) TotalCost
from #EmployeeMaster
Group BY
  GROUPING SETS
  (
    (EmployeeCode, DepartmentCode, LocationCode)

   ,(DepartmentCode)
   ,(LocationCode)
   ,()      
  )

Conclusion

Using GROUPING SETS, we can write multiple "Group By" clauses within a single query and get a single result set. Also it can be used as equivalent to as well as with ROLLUP and CUBE.