select deptno, job, sum(sal)
from emp
group by deptno, job
union all
select deptno, null, sum(sal)
from emp
group by deptno
union all
select null, null, sum(sal)
from emp;
위의 쿼리를 다음처럼 변경해 볼 수 있다. ^^
-- 1단계
select *
from (select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job) e,
(select 1 as no from dual
union all
select 2 from dual
union all
select 3 from dual)
order by no;
-- 2단계
select decode(no, 1, '집계', 2, '소계', '총계') as gubun,
decode(no, 1, deptno, 2, deptno) as deptno,
decode(no, 1, job) as job,
sum_sal,
no
from (select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job) e,
(select 1 as no from dual
union all
select 2 from dual
union all
select 3 from dual)
order by 1, 2;
-- 최종
select decode(no, 1, '집계', 2, '소계', '총계') as gubun,
decode(no, 1, deptno, 2, deptno) as deptno,
decode(no, 1, job) as job,
sum(sum_sal)
from (select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job) e,
(select 1 as no from dual
union all
select 2 from dual
union all
select 3 from dual)
group by decode(no, 1, '집계', 2, '소계', '총계'),
decode(no, 1, deptno, 2, deptno),
decode(no, 1, job)
order by 2, 3, 4;
| GUBUN | DEPTNO | JOB | SUM(SUM_SAL) |
|---|---|---|---|
| 집계 | 10 | CLERK | 1300 |
| 집계 | 10 | MANAGER | 2450 |
| 집계 | 10 | PRESIDENT | 5000 |
| 소계 | 10 | 8750 | |
| 집계 | 20 | ANALYST | 6000 |
| 집계 | 20 | CLERK | 1900 |
| 집계 | 20 | MANAGER | 2975 |
| 소계 | 20 | 10875 | |
| 집계 | 30 | CLERK | 950 |
| 집계 | 30 | MANAGER | 2850 |
| 집계 | 30 | SALESMAN | 5600 |
| 소계 | 30 | 9400 | |
| 총계 | 29025 |
출처 : http://blog.naver.com/orapybubu/40045220451