소계 & 총계 구하는 SQL (ROLLUP)

▨ 소계 & 총계 구하는 SQL (ROLLUP)

-- 다음의 [1][2][3]은 동일한 결과를 보여준다.

[1] rollup 이용
select decode(grouping(d.dname), 1, '총계',d.dname) dept,
         decode(grouping(t.job),       1, '계',    t.job)       job,
         nvl(sum(t.sal),0)         salary
  from dept d, emp t
 where t.deptno = d.deptno
 group by rollup(d.dname, t.job)
 order by 1,2;
 

[2] COPY_T 대신 무한로우 생성 쿼리를 이용
select decode(no,1,trim(d.dname),2,trim(d.dname),'총계') dept,
         decode(no,1,trim(t.job),                             '계')    job,
         nvl(sum(t.sal),0) salary
  from (select level no from dual connect by level <= 3), dept d, emp t
 where t.deptno = d.deptno
 group by decode(no,1,trim(d.dname),2,trim(d.dname),'총계'),
              decode(no,1,trim(t.job),                             '계')
 order by 1,2;

 

[3] 가장 접근하기 쉬운 무식한 방법
select d.dname dept, t.job job, nvl(sum(t.sal),0) salary
  from dept d, emp t
 where t.deptno = d.deptno
 group by d.dname, t.job
union all
select d.dname dept, '계' job, nvl(sum(t.sal),0) salary
  from dept d, emp t
 where t.deptno = d.deptno
 group by d.dname
union all
select '총계' dept, '계' job, nvl(sum(t.sal),0) salary
  from emp t
 order by 1,2;

 

▶ 결과

DEPT             JOB             SALARY
----------------------------------
ACCOUNTING CLERK         1300
ACCOUNTING MANAGER    2450
ACCOUNTING PRESIDENT   5000
ACCOUNTING 계                8750
RESEARCH    ANALYST      6000
RESEARCH    CLERK         1900
RESEARCH    MANAGER    2975
RESEARCH    계                10875
SALES          CLERK          950
SALES          MANAGER     2850
SALES          SALESMAN   5600
SALES          계                9400
총계              계                29025

 

 

▨ 각 항목별 소계 & 총계 구하는 SQL (CUBE)

-- 다음의 [1][2][3]은 동일한 결과를 보여준다.

 

[1] cube 이용
select decode(grouping(d.dname), 1, '총계',d.dname) dept,
         decode(grouping(t.job),       1, '계',    t.job)      job,
         nvl(sum(t.sal),0) salary
  from dept d, emp t
 where t.deptno = d.deptno
 group by cube(d.dname, t.job)
 order by 1,2;
 

[2] COPY_T 대신 무한로우 생성 쿼리를 이용
select decode(no,1,trim(d.dname),2,trim(d.dname),'총계') dept,
         decode(no,1,trim(t.job),      3,trim(t.job),      '계')     job,
         nvl(sum(t.sal),0) salary
  from (select level no from dual connect by level <= 4), dept d, emp t
 where t.deptno = d.deptno
 group by decode(no,1,trim(d.dname),2,trim(d.dname),'총계'),
              decode(no,1,trim(t.job),  3,trim(t.job),          '계')
 order by 1,2;

 

[3] 가장 접근하기 쉬운 무식한 방법
select d.dname dept, t.job, nvl(sum(t.sal),0) salary
  from dept d, emp t
 where t.deptno = d.deptno
 group by d.dname, t.job
union all
select d.dname dept, '계' job, nvl(sum(t.sal),0) salary
  from dept d, emp t
 where t.deptno = d.deptno
 group by d.dname
union all
select '총계' dept, t.job, nvl(sum(t.sal),0) salary
  from dept d, emp t
 where t.deptno = d.deptno
 group by t.job
 order by 1,2;

 

▶ 결과

DEPT             JOB             SALARY
----------------------------------
ACCOUNTING CLERK         1300
ACCOUNTING MANAGER    2450
ACCOUNTING PRESIDENT   5000
ACCOUNTING 계                8750
RESEARCH    ANALYST      6000
RESEARCH    CLERK         1900
RESEARCH    MANAGER    2975
RESEARCH    계                10875
SALES           CLERK         950
SALES           MANAGER    2850
SALES           SALESMAN   5600
SALES           계                9400
총계              ANALYST      6000
총계              CLERK          4150
총계              MANAGER     8275
총계              PRESIDENT   5000
총계              SALESMAN   5600
총계              계                29025

출처 : http://simmys.tistory.com/category/DataBase/Oracle