Cube and Rollup in SQL Server – SQL Server Tutorial

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

Output :-

• Deptno              job                 sumsal
•  Null                  NULL                  31025                  → this is total salary of (hole) all dept
• 10                      NULL                   7450                    → this is total sal of 10^th 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.