Saturday, October 26, 2013

SUPER AGGREGATES IN SQL SERVER




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

Kubernetes

Prerequisites We assume anyone who wants to understand Kubernetes should have an understating of how the Docker works, how the Docker images...