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 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.

Related Projects

Leave a Reply

Your email address will not be published. Required fields are marked *