Super aggregates:
Are used to calculate aggregates of aggregates
There are two super aggregates functions
Roll Up
Cube
When there is a single col in the group by than there is no difference between roll up and cube
But when there are multiple columns in group by than roll up will calculate aggregate of aggregate only at the end of the group created based on first column in the group by.
Whereas cube can calculate aggregate at the end of every group create based on every column in the group by.
Syntax:
Select */<collist> from <tablename>
[where condition]
Group by <collist> with roll up /cube [having condition] [order by]
Find the total salary paid to employee dept-wise and also find the total salary paid to employees of all department
select deptno, sum(Sal) from emp group by deptno with rollup
or with cube
Find total salary paid to employees job wise in each department and also find the total salary paid to employee dept-wise
select deptno,job,sum(sal)as totalsalary from emp group by deptno,job with rollup
Find the total salary paid to employees job-wise in each department and also find the total salary paid to employee deptwise and job wise
select deptno,job,sum(sal)as totalsalaryfordeptfrom emp group by deptno,job with cube
deptno-1 column
job-2 column
Cube example
The difference between cube and roll up
Roll up option calculates sub totals, based on first dimension.
Cube option calculates subtotals, based on both dimensions
In sql server 2005, both are Non-ANSI standard
In sql server 2008 both are ANSI standard
No comments:
Post a Comment
Thank you for visiting my blog