Fancy group by rollup example

Played around with group by rollup today and thought I'd post a little example.

Let's look at the basic usage of the rollup clause

select
  deptno,
  ename,
  sum(sal) sal from emp
  group by rollup (deptno,ename)
/

-- Here's the output from sqlplus

   DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        10 CLARK            2450
        10 MILLER           1300
        10                  8750
        20 FORD             3000
        20 ADAMS            1100
        20 JONES            2975
        20 SCOTT            3000
        20 SMITH             800
        20                 10875
        30 WARD             1250
        30 ALLEN            1600
        30 BLAKE            2850
        30 JAMES             950
        30 MARTIN           1250
        30 TURNER           1500
        30                  9400
                           29025

18 rows selected.

Ok, thats all well and cool. Oracle sums up the previous rows when the group by condition change, which in our case is for each individual and for the department. When a rollup happens all "non rolled up" columns are returned as nulls as shown in the above example. Notice the last row which has sum of all employees and hence nulls are returned for the two rollup columns.
You probably want to label the rows instead of the nulls with something more usefull, like a small "Sum" descriptor or something. As usuall, decode() is our friend.
Example with pretty labels:

with empt as
(
  select
  deptno,
  ename,
  sum(sal) sal from emp
  group by rollup (deptno,ename)
)
select deptno,
  decode(ename,
    null,
    decode(rownum,count(*) over (),'TOTAL SUM','DEPT SUM'),
    ename) ename,
  sal
from empt
/

-- And the output from sqlplus

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 KING             5000
        10 MILLER           1300
        10 DEPT SUM         8750
        20 ADAMS            1100
        20 FORD             3000
        20 JONES            2975
        20 SCOTT            3000
        20 SMITH             800
        20 DEPT SUM        10875
        30 ALLEN            1600
        30 BLAKE            2850
        30 JAMES             950
        30 MARTIN           1250
        30 TURNER           1500
        30 WARD             1250
        30 DEPT SUM         9400
           TOTAL SUM       29025

18 rows selected.

Cool.