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