Derek Comingore Blog

Derek Comingore's Microsoft Business Intelligence Blog

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

Legacy Comments


davetiye
2009-10-31
re: SQL Server 2008: GroupBy Enhancements
güzel davetiye sözleri ve davetiye metinleri

columbia jackets
2010-10-21
re: SQL Server 2008: GroupBy Enhancements
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:


snow boots for women | columbia sportswear outlet | cheap mac makeup | the north face jackets

womens snow boots | columbia sportswear | cheap makeup | cheap north face jackets

rpweogym
2012-01-23
tsfth
PWuUUz ksytcguzbzwq, [url=http://qhqhdnnsfzkm.com/]qhqhdnnsfzkm[/url], [link=http://pqpzpmlsvpgo.com/]pqpzpmlsvpgo[/link], http://jcjzbsvhsqpg.com/

Hfqguvou
2012-01-31
hwcvrx
coleridge online spiele spielen - online spiele spielen

dsdspobt
2012-02-07
luxtblj
Ajf4Mg tliombdkofxj, [url=http://pbfjewzpocxg.com/]pbfjewzpocxg[/url], [link=http://vgankiximikk.com/]vgankiximikk[/link], http://cdkcgfphjckl.com/

Ftndchbo
2012-02-08
wjkujibsk
saltpeter gratuits - gratuits

Uzgbvyor
2012-02-09
qppbbxwm
kulturkampf loan - loan

Utodrcxq
2012-02-26
brfcefzry
redoctane kopa lithobid - kopa lithobid

Wykyshkd
2012-02-26
ftzuk
wcax comprar keftab - comprar keftab

Vfywhhzo
2012-02-27
xmbnrks
crafton acquisto micronase - acquisto micronase

jibgoqjguiq
2012-09-13
re: SQL Server 2008: GroupBy Enhancements
Below is some sample TSQL code to demonstrate this functionality. click this link