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)


            ()         


      )


 

Comments (3)

  1. roopa says:

    SELECT Region, Department_number, avg(salary) Average_Salary

    from Employee

    Group BY

               (Department_number)

    This query will generate an error.. Region is not in the select list or aggregate?

  2. Songokute says:

    What this code means:

    GROUPING SETS

         (

               (Region, Department_Number),

               (Region)

               ()        

         )

    How different if we use this:

    GROUPING SETS

         (            

               (Region).

               (Region, Department_Number),

               ()        

         )

Skip to main content