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
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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))
Code

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)
Code

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
Code

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), ())
Code


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.