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