Jerry Nixon on Windows: SQL Server 2008 new “grouping” tSQL candy

Jerry Nixon on Windows

Monday, June 8, 2009

SQL Server 2008 new “grouping” tSQL candy

So many new features in SQL 2008. I presented on the updates at Trifecta a few months ago and never got around to posting some of my samples on my blog. Sorry about that if you were looking. Here’s the bit about grouping syntax. The code should create the sample table and data for you and run right away.

insert into Sales values
(1999, 'Q1', 'Jerry', 123)
,(1999, 'Q2', 'Jerry', 234)
,(1999, 'Q3', 'Jerry', 345)
,(1999, 'Q4', 'Jerry', 456)

select * from
(
values (1, 2), (2, 3), (3, 4)
) as DerivedTable(Col1, Col2)

-- ROLLUP = Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.

select
Year
,AVG(Amount) as AverageSales
from
Sales
group by
Year WITH ROLLUP

-- GROUPING = Indicates whether a specified column expression in a GROUP BY list is aggregated or not.

select
Year
,AVG(Amount) as Average
,GROUPING(Year) as [YearRollUp?]
from
Sales
group by
Year WITH ROLLUP

-- GROUPING SET = Specifies multiple groupings of data in one query.

select
Year
,Quarter
,AVG(Amount) as Average
,GROUPING(Year) as [YearRollUp?]
from
Sales
group by
GROUPING SETS ((Year, Quarter))

select
Year
,Quarter
,AVG(Amount) as Average
,GROUPING(Year) as [YearRollUp?]
from
Sales
group by
GROUPING SETS ((Year, Quarter), (Year), ())

-- GROUPING_ID = Is a function that computes the level of grouping.

select
Year
,Quarter
,AVG(Amount) as Average
,GROUPING_ID(Year, Quarter) as [x]
from
Sales
group by
GROUPING SETS ((Year, Quarter), (Year), ())

-- use it in the having

declare @level int = 1

select
Year
,Quarter
,AVG(Amount) as Average
,GROUPING_ID(Year, Quarter) as [x]
from
Sales
group by
GROUPING SETS ((Year, Quarter), (Year), ())
having
GROUPING_ID(Year, Quarter) = @level


-- CUBE = Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.

select
Year
,Quarter
,AVG(Amount) as Average
from
Sales
group by
cube(Year, Quarter, SalesPerson)
order by
Year, Quarter, Average