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.
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
The resultset on executing this query is
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 NULL, NULL, avg(salary) Average_Salary
SELECT Region, Department_Number, avg(sal) Average_Salary
CUBE (Region, Department_Number)
The above query is equivalent to the following query:
ROLLUP (Region, Department_Number)