Oracle SQL :ROLL UP function

When to Use ROLLUP

• It is very helpful for subtotaling along a hierarchical dimension such as time or geography. For instance, a query could specify a ROLLUP of year/month/day or country/state/city.

• It simplifies and speeds the population and maintenance of summary tables. Data warehouse administrators may want to make extensive use of it. Note that population of summary tables is even faster if the ROLLUP query executes in parallel.

SQL> select sum(sal) ,deptno
  2  ,job from emp group by rollup(deptno,job);
  SUM(SAL)     DEPTNO JOB
---------- ---------- ---------
      5000         10 SALESMAN
      5000         10
      1450         30 CLERK
      4350         30 MANAGER
     10250         30 SALESMAN
     16050         30
      2000         40 SALESMAN
      2000         40
      9600         50 CLERK
      7000         50 ANALYST
     16600         50
  SUM(SAL)     DEPTNO JOB
---------- ---------- ---------
     39650
x

Use the ROLLUP extension in tasks involving subtotals.