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)

            ()         

      )