SQL Server 2008: GroupBy Enhancements

Microsoft SQL Server 2008 introduces several enhancements & changes to how we group and aggregate relational result sets in TSQL. What follows is a simplistic listing and overview of the grouping/aggregate changes we are seeing in Microsoft SQL Server 2008 CTP5:

1.       New GROUPING SETS Operator

2.       ROLLUP & CUBE Operator Syntax Changes

3.       New GROUPING_ID() Function

 

GROUP BY Operator Addition: GROUPING SETS

The first and most noted change in how we can perform relational grouping is the GROUPING SETS operator addition to the GROUP BY clause. This new operator allows you to perform several grouping set in one query. The older equivalent of this functionality is to perform several GROUP BY queries and then perform a UNION ALL between them. The GROUPING SETS operator supports concatenating column groupings and an optional grand total row. GROUPING SETS can be used in conjunction with the ROLLUP & CUBE operators (which we will use the new syntax described below for ROLLUP|CUBE). Below is some sample TSQL code to demonstrate this functionality:

 

*All sample code requires the AdventureWorks & AdventureWorks DW sample databases.

 

USE AdventureWorksDW;

GO

--------OLD METHOD, UNION ALL

SELECT NULL AS [ProductKey], OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY OrderDateKey, DueDateKey, ShipDateKey

UNION ALL

SELECT ProductKey, NULL AS [OrderDateKey], DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY ProductKey, DueDateKey, ShipDateKey

UNION ALL

SELECT ProductKey, OrderDateKey, NULL AS [DueDateKey], ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY ProductKey, OrderDateKey, ShipDateKey

UNION ALL

SELECT ProductKey, OrderDateKey, DueDateKey, NULL AS [ShipDateKey]

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY ProductKey, OrderDateKey, DueDateKey

GO

--------NEW METHOD, GROUPING SETS

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY GROUPING SETS(ProductKey, OrderDateKey, DueDateKey, ShipDateKey);

GO

--------NEW METHOD, GROUPING SETS w/Grand Total Row (record #1125 is the grand total row)

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY GROUPING SETS(ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ());

GO

--------NEW METHOD, GROUPING SETS w/Composite Columns

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY GROUPING SETS((ProductKey, OrderDateKey), DueDateKey, ShipDateKey);

GO

--------NEW METHOD, GROUPING SETS w/ROLLUP Composite Columns

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY GROUPING SETS(ROLLUP(ProductKey, OrderDateKey), DueDateKey, ShipDateKey);

GO

--------NEW METHOD, GROUPING SETS w/CUBE Composite Columns

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY GROUPING SETS(CUBE(ProductKey, OrderDateKey), DueDateKey, ShipDateKey);

GO

 

GROUP BY Operator Syntax Changes: ROLLUP & CUBE

Next up are the changes in the ROLLUP|CUBE operators' syntax. These operators produce aggregate groupings and are appended to the GROUP BY clause. Prior to SQL Server 2008 you would specify WITH ROLLUP|WITH CUBE. In SQL Server 2008, you will first designate ROLLUP|CUBE and then pass the grouped columns into these operators. Below is some sample TSQL code to demonstrate this functionality:

 

USE AdventureWorksDW;

GO

--------OLD METHOD, WITH ROLLUP

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY ProductKey, OrderDateKey, DueDateKey, ShipDateKey WITH ROLLUP;

GO

--------NEW METHOD, ROLLUP()

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY ROLLUP(ProductKey, OrderDateKey, DueDateKey, ShipDateKey);

GO

--------OLD METHOD, WITH CUBE

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY ProductKey, OrderDateKey, DueDateKey, ShipDateKey WITH CUBE;

GO

--------NEW METHOD, CUBE()

SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey

    ,SUM (SalesAmount) AS [Sales]

FROM dbo.FactInternetSales

GROUP BY CUBE(ProductKey, OrderDateKey, DueDateKey, ShipDateKey);

GO

 

New Function: GROUPING_ID()

Last but not least, there is a new function, GROUPING_ID which returns the level of grouping for a particular column. This function is an enhancement from the older GROUPING() function. GROUPING() returns a 0 or a 1 to indicate if the column is aggregated or not. Below is some sample TSQL code to demonstrate this functionality:

 

USE AdventureWorks;

GO

SELECT

     D.Name

    ,GROUPING_ID(D.Name, E.Title) AS [Name_Title_GroupID]

    ,GROUPING(D.Name) AS [Name_GroupAggregated?]

    ,GROUPING(E.Title) AS [Title_GroupAggregated?]

    ,COUNT(E.EmployeeID) AS N'Employee Count'

FROM HumanResources.Employee E

   INNER JOIN HumanResources.EmployeeDepartmentHistory DH

        ON E.EmployeeID = DH.EmployeeID

    INNER JOIN HumanResources.Department D

        ON D.DepartmentID = DH.DepartmentID    

WHERE DH.EndDate IS NULL

    AND D.DepartmentID IN (12,14)

GROUP BY ROLLUP(D.Name, E.Title);