SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



Group By Grouping Sets - MS SQL Server 2008 T-SQL Improvements


One of the new t-sql enhancements introduced for SQL developers with Microsoft SQL Server 2008 is the new GROUPING SETS extension to the GROUP BY clause.

Actually GROUPING SETS syntax improvement in T-SQL enables sql developers to work easily on ROLLUP and CUBE like sql statements.

GROUPING SETS extension to GROUP BY clause can be considered as a more general parametric method for CUBE and ROLLUP sql commands.





Below there are two result sets that display numbers of Customers in different cities and countries.

SELECT
 Country, City, COUNT(CustomerAddress) CountCustomer
FROM (
 SELECT
  Countries.Country,
  Cities.City,
  Addresses.Id CustomerAddress
 FROM Addresses
 INNER JOIN Cities ON Cities.Id = Addresses.CityId
 INNER JOIN Countries ON Countries.Id = Cities.CountryId
) Cnt
GROUP BY GROUPING SETS(Country,City,(),(Country,City))

group by grouping sets

Actually the same result set can be obtained by using the CUBE sql statement syntax as follows.

SELECT
 Country, City, COUNT(CustomerAddress) CountCustomer
FROM (
 SELECT
  Countries.Country,
  Cities.City,
  Addresses.Id CustomerAddress
 FROM Addresses
 INNER JOIN Cities ON Cities.Id = Addresses.CityId
 INNER JOIN Countries ON Countries.Id = Cities.CountryId
) Cnt
GROUP BY CUBE(Country,City)

In fact, the above CUBE syntax (GROUP BY CUBE(Country,City)) is also new with SQL Server 2008 when compared with the previous version which also runs on MS SQL2008

GROUP BY Country,City WITH CUBE

The same syntax changes are also valid for ROLLUP sql statement just as CUBE.

So if we get similar results using ROLLUP, CUBE with results using GROUPING SETS, why we use the new GROUP BY GROUPING SETS extensions?

Because we are as sql developers are now free to define the grouping expressions in the grouping sets, we can omit unwanted grouping sets by not declaring them in the Group By Grouping Sets declaration

SELECT
 Country,
 City,
 COUNT(CustomerAddress) CountCustomer
FROM (
 SELECT
  Countries.Country,
  Cities.City,
  Addresses.Id CustomerAddress
 FROM Addresses
 INNER JOIN Cities ON Cities.Id = Addresses.CityId
 INNER JOIN Countries ON Countries.Id = Cities.CountryId
) Cnt
GROUP BY GROUPING SETS (Country, (Country, City), ())





Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems