Friday, July 1, 2011

Use of Rollup, Grouping, Grouping_ID and Cube in Group By


ROLLUP = Generates the simple GROUP BY aggregate rows
, plus subtotal or super-aggregate rows
, and also a grand total row.
GROUPING = Indicates whether a specified column expression in a GROUP BY list is aggregated or not.
GROUPING_ID = Is a function that computes the level of grouping.
CUBE = Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.

Sample Data

DECLARE @Sales TABLE (Year INT, Quarter VARCHAR(50), SalesPerson VARCHAR(50), Amount FLOAT)

INSERT INTO @Sales values
(1998, 'Q1', 'SalePerson1', 123)
, (1998, 'Q2', 'SalePerson2', 234)
, (1998, 'Q3', 'SalePerson4', 345)
, (1998, 'Q4', 'SalePerson3', 556)
, (1999, 'Q1', 'SalePerson1', 623)
, (1999, 'Q2', 'SalePerson2', 734)
, (1999, 'Q3', 'SalePerson3', 845)
, (1999, 'Q4', 'SalePerson4', 956)

Example 1 : (Single field in grouping)

SELECT
[Year]
, AVG(Amount) as Average
, GROUPING([Year]) as [YearRollUp?]
FROM
@Sales
GROUP BY
[Year] WITH ROLLUP

Output :

Year Average YearRollUp?
----------- ---------------------- -----------
1998 314.5 0
1999 789.5 0
NULL 552 1

Example 2 : (Multipal field in grouping)

SELECT
[Year]
, Quarter
, AVG(Amount) as Average
, GROUPING( [Year] ) as [YearRollUp?]
FROM
@Sales
GROUP BY
GROUPING SETS (( [Year], Quarter), ( [Year] ), ())

Output :

Year Quarter Average YearRollUp?
----------- ------- ---------------------- -----------
1998 Q1 123 0
1998 Q2 234 0
1998 Q3 345 0
1998 Q4 556 0
1998 NULL 314.5 0
1999 Q1 623 0
1999 Q2 734 0
1999 Q3 845 0
1999 Q4 956 0
1999 NULL 789.5 0
NULL NULL 552 1

Example 3 :( Example of Grouping_ID )

SELECT
[Year]
, Quarter
, AVG(Amount) as Average
, GROUPING_ID( [Year], Quarter) as [x]
FROM
@Sales
GROUP BY
GROUPING SETS (( [Year], Quarter), ( [Year] ), ( [Quarter] ), ())

Output :

Year Quarter Average x
----------- ------- ---------------------- -----------
1998 Q1 123 0
1999 Q1 623 0
NULL Q1 373 2
1998 Q2 234 0
1999 Q2 734 0
NULL Q2 484 2
1998 Q3 345 0
1999 Q3 845 0
NULL Q3 595 2
1998 Q4 556 0
1999 Q4 956 0
NULL Q4 756 2
NULL NULL 552 3
1998 NULL 314.5 1
1999 NULL 789.5 1

Example 4:(Example of Cube)

SELECT
[Year]
, [Quarter]
, AVG(Amount) as Average
, GROUPING_ID( [Year], [Quarter]) as [x]
FROM
@Sales
GROUP BY
CUBE( [Year], [Quarter])

Output :

Year Quarter Average x
----------- ------- ---------------------- -----------
1998 Q1 123 0
1999 Q1 623 0
NULL Q1 373 2
1998 Q2 234 0
1999 Q2 734 0
NULL Q2 484 2
1998 Q3 345 0
1999 Q3 845 0
NULL Q3 595 2
1998 Q4 556 0
1999 Q4 956 0
NULL Q4 756 2
NULL NULL 552 3
1998 NULL 314.5 1
1999 NULL 789.5 1