Grouping records by means of GROUPING SETS operator in GROUP BY clause

Posted: July 21, 2012 in MSSQL
Tags:

Task: to give a basic look at the grouping of rows in SQL Server



Whenever any aggregation function is used, the GROUP BY clause is required. SQL Server 2005 brought the GROUP BY operators like CUBE and ROLLUP, respectively. In addition, SQL Server 2008 brought the GROUP BY operator GROUPING SETS. The GROUPING SETS operator aggregates only specified groups instead of full sets of aggregations by means of CUBE or ROLLUP operators. As an alternative solution for grouping rows in terms of GROUPING SETS operator, one of SET operators was used before SQL Server 2008, the UNION concretely.


USE AdventureWorks2012
GO

SELECT  Name,
        CountryRegionCode,
        SalesLastYear
FROM    Sales.SalesTerritory
GO

Source code 1: Basic example skeleton


SSMS result – Basic example skeleton

Figure 1: SSMS result – Basic example skeleton



USE AdventureWorks2012
GO

SET STATISTICS IO ON
GO

SELECT   Name,
         CountryRegionCode,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY Name,
         CountryRegionCode

UNION ALL

SELECT   Name,
         NULL,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY Name

UNION ALL

SELECT   NULL,
         CountryRegionCode,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY CountryRegionCode

UNION ALL

SELECT   NULL,
         NULL,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GO

SET STATISTICS IO OFF
GO

Source code 2: Grouping by means of UNION operator before the GROUPING SETS operator in SQL Server 2008


SSMS result – Grouping by means of UNION operator before the GROUPING SETS operator in SQL Server 2008

Figure 2: SSMS result – Grouping by means of UNION operator before the GROUPING SETS operator in SQL Server 2008



USE AdventureWorks2012
GO

SET STATISTICS IO ON
GO

SELECT   Name,
         CountryRegionCode,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY
         GROUPING SETS
         (
             (Name, CountryRegionCode),
             (Name),
             (CountryRegionCode),
             ()
         )
ORDER BY GROUPING(Name),
         GROUPING(CountryRegionCode)
GO

SET STATISTICS IO OFF
GO

Source code 3: Grouping by means of GROUPING SETS operator in SQL Server 2008


SSMS result – Grouping by means of GROUPING SETS operator in SQL Server 2008

Figure 3: SSMS result – Grouping by means of GROUPING SETS operator in SQL Server 2008



USE AdventureWorks2012
GO

SET STATISTICS IO ON
GO

SELECT   Name,
         CountryRegionCode,
         SUM(SalesLastYear) AS TotalOfSalesLastYear
FROM     Sales.SalesTerritory
GROUP BY
         CUBE (Name, CountryRegionCode)
ORDER BY GROUPING(Name),
         GROUPING(CountryRegionCode)
GO

SET STATISTICS IO OFF
GO

Source code 4: Grouping by means of CUBE operator in SQL Server 2005


SSMS result – Grouping by means of CUBE operator in SQL Server 2005

Figure 4: SSMS result – Grouping by means of CUBE operator in SQL Server 2005



The result of IO resources consumption using the UNION ALL operator
by returning 27 rows:
Table 'SalesTerritory'. Scan count 4, logical reads 8,
                        physical reads 0, read-ahead reads 0,
                        lob logical reads 0, lob physical reads 0,
                        lob read-ahead reads 0.

The result of IO resources consumption using the GROUPING SETS operator
by returning 27 rows:
Table 'SalesTerritory'. Scan count 2, logical reads 44,
                        physical reads 0, read-ahead reads 0,
                        lob logical reads 0, lob physical reads 0,
                        lob read-ahead reads 0.

The result of IO resources consumption using the CUBE operator
by returning 27 rows:
Table 'SalesTerritory'. Scan count 2, logical reads 44,
                        physical reads 0, read-ahead reads 0,
                        lob logical reads 0, lob physical reads 0,
                        lob read-ahead reads 0.

Source code 5: Results comparison of IO resources consumption across particular SQL Server operators


Additional references:
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s