Grouping Sets in SQL Server 2008

Introduction:

This post describes a new feature in SQL Server 2008 – Grouping Sets

The result set returned by GROUPING SET is the union of the aggregates based on the columns specified in each set in the Grouping set.

Whenever an aggregate function is required, GROUPBY clause is the only solution. There can be a requirement to get these aggregate function based on different set of columns in the same result set.

We can able to get the same result using UNION operator with different queries.

It is an ISO Compliant feature.

Example:

Create a table as follows:

CREATE TABLE [dbo].[employee](

                [Employee_Number] [int] NOT NULL PRIMARY KEY,

                [Employee_Name] [varchar](30) NULL,

                [Salary] [float] NULL,

                [Department_Number] [int] NULL,

                [Region] [varchar](30) NULL

) ON [PRIMARY]

Now poplulate the table with some rows:

insert into employee values(1,'Sreekar',9000,10,'Hyderabad')

insert into employee values(2,'Raghu',5000,10,'Bangalore')

insert into employee values(3,'Kishore',4000,20,'Hyderabad')

insert into employee values(4,'Murali',8000,10,'Chennai')

insert into employee values(5,'Rajesh',8000,20,'Chennai')

After populating  some rows, we select some rows using Grouping Sets with the following query:

SELECT Region, Department_number, avg(salary) Average_Salary

from Employee

Group BY

                GROUPING SETS

                (

                                (Region, Department_number),

                                (Region),

                                (Department_number) ,

                                ()                            

                )

The resultset on executing this query is

Region	Department_number	Average_Salary
Bangalore	10	5000
Chennai	10	8000
Hyderabad	10	9000
NULL	10	7333.333333
Chennai	20	8000
Hyderabad	20	4000
NULL	20	6000
NULL	NULL	6800
Bangalore	NULL	5000
Chennai	NULL	8000
Hyderabad	NULL	6500

The result set contains rows grouped by each set in the specified in the Grouping Sets. You can see average salary of employees for each region and department. We can also get the average salary of the organization (Region and Department_Number  are NULL  in this case) . This was the result of empty groping set ().

One can get the same result achieved in SQL Server 2005 using the following query:

SELECT Region, Department_number, avg(salary) Average_Salary

from Employee

Group BY

            (Region, Department_number)

UNION

SELECT Region, Department_number, avg(salary) Average_Salary

from Employee

Group BY

            (Region)

UNION

SELECT Region, Department_number, avg(salary) Average_Salary

from Employee

Group BY

            (Department_number)

UNION

SELECT NULL, NULL, avg(salary) Average_Salary

from Employee

     

CUBE  REPLACEMENT:

SELECT Region, Department_Number, avg(sal) Average_Salary

from Employee

Group BY

      CUBE (Region, Department_Number)

The above query is equivalent to the following query:

SELECT Region, Department_Number, avg(sal) Average_Salary

from Employee

GROUPING SETS

      (

            (Region, Department_Number),

            (Region),

            (Department_Nuber) ,

            ()         

      )

ROLLUP  REPLACEMENT:

SELECT Region, Department_Number, avg(sal) Average_Salary

from Employee

Group BY

      ROLLUP (Region, Department_Number)

The above query is equivalent to the following query:

SELECT Region, Department_Number, avg(sal) Average_Salary

from Employee

GROUPING SETS

      (

            (Region, Department_Number),

            (Region)

            ()         

      )