Cube and Rollup :-
Both options are used to calculate sub_totals
Select deptno,job,sum(sal) sumsal from emp group by deptno, job with rollup order by deptno,job
- Deptno job sumsal
- Null NULL 31025 → this is total salary of (hole) all dept
- 10 NULL 7450 → this is total sal of 10th dept
- 10 HR 5000
- 10 Manager 2450
- 20 NULL 9000 → like wise
- 20 Clerk 4000
- 20 Manager 5000
Cube example :-
Select deptno,job,sum(sal),sumsal from emp group by dept no,job with cube order by deptno,job
The difference between rollup & cube is:
- Rollup option calculate subtotals based on first dimension.
- Cube option calculates subtotals based on both dimensions
- In sql server 2005, both rollup & cube is non-ansi standard.
- In sql server 2008, both are ansi standard.
Select deptno,job sum(sal) sumsal from emp group by rollup(deptno,job) order by deptno,job —-> Ansi standard.