SELECT SUBSTR(DECODE(TO_CHAR(deptno),'0','大合計',TO_CHAR(deptno)),1,4) 部門番号, DECODE(job,'0','合計',job) 職業, sumsal 給料 FROM ( SELECT deptno,job,sumsal FROM ( SELECT NVL(deptno,0) deptno,NVL(job,'0') job,SUM(sal) sumsal FROM emp GROUP BY ROLLUP(deptno,job) ORDER BY deptno,job ) ORDER BY deptno,job,sumsal DESC ); |
部門番号 | 職業 | 給料 |
大合計 | 合計 | 24925 |
10 | 合計 | 8750 |
10 | CLERK | 1300 |
10 | MANAGER | 2450 |
10 | PRESIDENT | 5000 |
20 | 合計 | 6775 |
20 | ANALYST | 3000 |
20 | CLERK | 800 |
20 | MANAGER | 2975 |
30 | 合計 | 9400 |
30 | CLERK | 950 |
30 | MANAGER | 2850 |
30 | SALESMAN | 5600 |