This article includes information about a feature from SQL Server 2008 (and up) - Group by extensions.

Table of Contents


 

Problem Definition

In SQL Server 2005 and earlier versions, we would have faced the situation that to get sum or avg or max based on more than one column.

The only way to go, GROUP BY with multiple queries and Union them at final which leads to performance issues.

 

Solution

  • SQL Server 2008 added new wings to the Group By clause to do multiple GroupBy on the single query.

—  New Wings of Group By:

◦       GROUPING SETS,

◦       CUBE

◦       ROLLUP

 

@Population table variable script

The below examples are using @Population table variable. The script mentioned below for the table variable generation.

DECLARE @Population TABLE (Country VARCHAR(100),[State] VARCHAR(100),City VARCHAR(100),Population Decimal (6,1))
INSERT INTO @Population VALUES('India', 'Delhi','East Delhi',9 )
INSERT INTO @Population VALUES('India', 'Delhi','South Delhi',8 )
INSERT INTO @Population VALUES('India', 'Delhi','North Delhi',5.5)
INSERT INTO @Population VALUES('India', 'Delhi','West Delhi',7.5)
INSERT INTO @Population VALUES('India', 'Karnataka','Bangalore',9.5)
INSERT INTO @Population VALUES('India', 'Karnataka','Belur',2.5)
INSERT INTO @Population VALUES('India', 'Karnataka','Manipal',1.5)
INSERT INTO @Population VALUES('India', 'Maharastra','Mumbai',30)
INSERT INTO @Population VALUES('India', 'Maharastra','Pune',20)
INSERT INTO @Population VALUES('India', 'Maharastra','Nagpur',11 )
INSERT INTO @Population VALUES('India', 'Maharastra','Nashik',6.5) 

 

Grouping Sets

 Simply list all grouping sets that you need

 SELECT Country,[State],City,

SUM (Population) AS [Population]

FROM @Population

GROUP BY GROUPING SETS ((Country,[State]),([State],City))

 

Cube:

It produces all possible grouping sets that can be formed out of the elements listed in parentheses, including the empty grouping set

SELECT Country,[State],City,

SUM (Population) AS [Population]

FROM @Population

GROUP BY Country,[State],City WITH CUBE

 
Roll Up

The ROLLUP sub clause produces only the grouping sets that have business value, assuming a hierarchy between the elements.

 SELECT Country,[State],City,

SUM (Population) AS [Population]

FROM @Population

GROUP BY Country,[State],City WITH ROLLUP

 

Conclusion

 Use the wings to fly on performance.

-Sugumar Pannerselvam


References