Friday, August 6, 2010

Understanding the Aggregation WITH ROLLUP Operator and CUBE Operator in SQL Server 2008

Rollup Operator adds rows with summaries. Last row is “grand total”. Adds summary rows at each group level . NULL values in the grouped columns, can be overridden by defining GROUPING Columns, or can sort summaries to top.

CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 16000)
INSERT Sales VALUES(1, 2006, 15000)
INSERT Sales VALUES(1, 2007, 35000)
INSERT Sales VALUES(2, 2005, 25000)
INSERT Sales VALUES(2, 2006, 26000)
INSERT Sales VALUES(3, 2006, 21000)
INSERT Sales VALUES(3, 2007, 29000)

Command in Aggregation WITH ROLLUP.

SELECT EmpId, Yr, SUM(Sales) AS Sales
    FROM Sales
    GROUP BY ROLLUP(EmpId, Yr)

The result is displayed below with Aggregation WITH ROLLUP  for the query on table sales.

Command in Aggregation WITH CUBE

SELECT EmpId, Yr, SUM(Sales) AS Sales
    FROM Sales
    GROUP BY CUBE(EmpId, Yr)

The result is displayed below WITH CUBE Aggregation for the table sales.

No comments:

Recent Posts