Aggregation is primary need when you are building reports. The simple GROUP BY is not sufficient when you are building reports. The ROLLUP and CUBE are handy features for cumulative aggregates. These ROLLUP and CUBE are replaced with GROUPING SET in SQL 2008.
The features like ROLLUP, CUBE exists for
backward compatibility, so in if you are in SQL 2008 start using GROUPING SET.
Instead of explaining GROUPING SET, I just listed set of query and output which is self explanatory. .
|
Sample Table
|
|
||||||||||||||||||||||||||||||||||||
|
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP
OR
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY ROLLUP(EmpId, Yr)
|
|
||||||||||||||||||||||||||||||||||||
|
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH CUBE
OR
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY CUBE(EmpId, Yr)
|
|
||||||||||||||||||||||||||||||||||||
|
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), ())
|
|
||||||||||||||||||||||||||||||||||||
|
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId))
|
|
||||||||||||||||||||||||||||||||||||
|
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId))
|
|
||||||||||||||||||||||||||||||||||||
|
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId), (Yr))
|
|
||||||||||||||||||||||||||||||||||||
|
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr))
|
|
||||||||||||||||||||||||||||||||||||
|
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId) )
OR
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId), (Yr, EmpId))
|
|
No comments:
Post a Comment