Oracle Advanced SQL 강좌
Rollup 예제 30 20 99,999+

by 김정식 ROLLUP [2003.11.19]


ROLLUP operator

ROLLUP구문은 GROUP BY 절과 같이 사용 되며, GROUP BY절에 의해서 그룹 지어진 집합 결과에 대해서 좀 더 상세한 정보를 반환하는 기능을 수행 한다.

SELECT절에 ROLLUP을 사용함으로써 보통의 SELECT된 데이터와 그 데이터의 총계를 구할 수 있다.

간단 예제
 
-- 먼저 GROUP BY를 사용해서 직업별로 급여 합계를 구하는 예제이다.
SQL> SELECT job, SUM(sal)
     FROM emp
     GROUP BY job; 

JOB          SUM(SAL)
---------- ----------
ANALYST         600
CLERK          3200
MANAGER       33925
PRESIDENT      5000
SALESMAN       4000


-- ROLLUP을 사용해서 직업별로 급여 합계와  총계를 구하는 예제이다.
SQL> SELECT job, SUM(sal)
     FROM emp
     GROUP BY ROLLUP(job);
 

JOB          SUM(SAL
---------- ----------
ANALYST         6000
CLERK           3200
MANAGER        33925
PRESIDENT       5000
SALESMAN        4000
               52125   --> 급여 합계에 대한 총계가 추가 되었다.
    

좀 더 복잡한 예제

부서별로 인원수와 급여 합계를 구하는 예제

 
-- GROUP BY절을 사용 한 결과
SQL> SELECT b.dname, a.job, SUM(a.sal) sal, 
            COUNT(a.empno) emp_count
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY b.dname, a.job

DNAME       JOB               SAL  EMP_COUNT
----------  ---------- ---------- ----------
ACCOUNTING  CLERK            1300          1
ACCOUNTING  MANAGER          2450          1
ACCOUNTING  PRESIDENT        5000          1
RESEARCH    ANALYST          6000          2
RESEARCH    CLERK            1900          2
RESEARCH    MANAGER          2975          1
SALES       MANAGER         28500          1
SALES       SALESMAN         4000          3


-- 부서별로 인원과, 급여합계가 한 눈에 보이지 않는다.
-- 일일이 부서에 해당하는 직업별 급여와 사원수를 일일이 더해야 한다.

-- 이런 경우 ROLLUP을 사용하여 쉽게 조회 할 수 있다. 
SQL> SELECT b.dname, a.job, SUM(a.sal) sal, 
            COUNT(a.empno) emp_count
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY ROLLUP(b.dname, a.job)


DNAME      JOB               SAL  EMP_COUNT
---------- ---------- ---------- ----------
ACCOUNTING CLERK            1300          1
ACCOUNTING MANAGER          2450          1
ACCOUNTING PRESIDENT        5000          1
ACCOUNTING                  8750          3  -->  ACCOUNTING 부서의 급여 합계와 전체 사원 수
RESEARCH   ANALYST          6000          2
RESEARCH   CLERK            1900          2
RESEARCH   MANAGER          2975          1
RESEARCH                   10875          5 -->  RESEARCH 부서의 급여 합계와 전체 사원 수
SALES      MANAGER         28500          1
SALES      SALESMAN         4000          3
SALES                      32500          4 -->  SALES부서의 급여 합계와 전체 사원 수
                               52125         12 ->  전체 급여 합계와 전체 사원 수
 

-- 위와 같이ROLLUP은 일반적인 누적에 대한 총계를 구할 때 아주 편리하게 사용 할 수 있다.
    

- 강좌 URL : http://www.gurubee.net/lecture/1845

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 김정식 [2004.05.07 09:24:39]
누접합 예제..

SELECT empno, SUM(sal) OVER (ORDER BY empno) "누적합"
FROM emp;

by goguy [2004.05.08 20:30:10]
SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY b.dname, a.job

분석해 볼까여
1.select;
2.from
3.where
4.group

2.테이블 두개가 메모리에 적제가 되겠죠
3.조건에 의해 deptno 을 기준으로 하나의 테이블로 정리 되겠죠
그다음은 4. 가 계산되는데
여기서 4.가 없다고 가정하면
1.로 가서 결국 하나의 행이 결과값으로 도출될 것입니다. 그룹함수에 의해서
총합과 행의 총갯수가 반환되겠죠
그럼 다시 되돌아가서
4.group by 절이 계산을 하게 되는데 우선 b.dname만 있다면 몇개의 레코드가 반환될까여 부서명이 4개라면.. 4개의 레코드가 반환되겠죠(단 4개의 부서가 데이타에 존재한다면)
그 다음에 a.job 까지(직업은 3개라고 가정하면) 두개의 조건이 주어졌습니다.
그럼 몇개의 레코드가 반환될까여 12개의 레코드가 반환될까여????

이번에는 문장을 분석해서 SQL을 작성해 볼까여 (위 강의가 기준)
부서의 직업별로 인원수와 급여 합계를 구하라

키포인트 인원수와 급여합계입니다.
여기서 그룹함수가 사용된다는 것을 알수 있고 GROUP BY절을 사용해야 된다는 것도 알수가 있습니다.
형식은
1.SELECT
2.TABLE
3.WHERE
4.GROUP BY
로 형식이 가추어 지고
1. 컬럼은 4개가 되고 그룹함수 SUM()가 COUNT()가 들어가고여
2. 테이블은 위 강의가 기준이니까 2개이고여
3. 자연스럽게 JOIN 이 일어나겠네여 무슨 조인일까여????
4. GROUP BY절에서 부서와 직업이 GROUP으로 묶여지겠네여...
SQL을 작성하면 위와 같겠지여



by 이성인 [2004.12.17 16:49:04]
SELECT NVL(DECODE(B.NO, '1', DNAME), '총계') DNAME,
SUM(CLERK) CLERK,
SUM(MANAGER) MANAGER,
SUM(ETC) ETC,
SUM(DEPT_SAL) DEPT_SAL
FROM (
SELECT B.DNAME, A.CLERK, A.MANAGER, A.ETC, A.CLERK + A.MANAGER + A.ETC DEPT_SAL
FROM (
SELECT DEPTNO,
SUM(DECODE(JOB,'CLERK',SAL)) CLERK,
SUM(DECODE(JOB,'MANAGER',SAL)) MANAGER,
SUM(DECODE(JOB,'MANAGER',0,'CLERK',0,SAL)) ETC
FROM EMP
GROUP BY DEPTNO
) A, DEPT B
WHERE A.DEPTNO = B.DEPTNO ) A,
(SELECT '1' NO FROM DUAL
UNION ALL
SELECT '2' FROM DUAL) B
GROUP BY DECODE(B.NO, '1',A.DNAME);

위 처럼 오라클에서 지원하는 함수를 이용하지 않고 만들어 봤어요..
참조 하세요.

by smile [2005.11.09 20:18:55]
퍼갑니다. ^^

by 시근땀 [2006.03.20 11:18:16]
이성인님 너무 어려워요.

초보자들 기 죽이시려는거죠? ㅠㅠ

by 2-2 [2006.05.11 13:44:27]
갈수록..오리무중이네...아..

by 꾸~~욱! [2006.07.21 01:55:18]
이것 저것 함수가 많이 들어 갔는데용... 머리아픔다...

by 물어 콱! [2007.11.13 10:00:46]
워매....바로..GG쳤음;ㅋㅋㅋ 이성인님 정말 어렵습니다...

by 최원령 [2008.01.19 16:43:25]
글쎄요..이성인님이 작성하신 부분은 JOB을 하드코딩 하신건데..JOB의 갯수를 레코드 수만큼 표현 할때는 안될거 같네요..

by 지나가는놈 [2009.02.16 20:10:15]
SELECT empno, SUM(sal) OVER () "누적합"
FROM emp;
이런 식으로 그냥 아무 조건없이 사용할 때는 over()에 아무 값도 안 집어넣어도 됩니다.

by 호레이시오 [2009.07.08 18:12:04]
쉽게 풀이되서 속이 다 시원혀요~

by 하루살이 [2009.08.05 15:24:23]
ROLLUP 예제에서

SELECT job, SUM(sal)
FROM emp
GROUP BY ROLLUP
그대로 쿼리문 실행하면 되나요..
나만 안되는건가..;;

by 하루살이 [2009.08.05 15:54:33]
rollup(job);

이렇게 하면 되군요 ㅎㅎ

초보라 ;; ㅎㅎ

by 임동후 [2010.01.26 17:37:05]
오 왜 안되나 멍때리다가 하루살이님때문에 알았음 ㅋ

by 양한마리 [2010.02.03 10:28:23]
이해하기 쉽네요 ^ ^

by 한반도 [2010.04.08 14:39:34]
위 예제에서 SUM값의 명칭을 원하는 걸로 변경을 하고자 할때 어떻게 변경을 할 수 있을까요? 총계 뭐이런 문구로요?

by rockgull [2010.04.22 15:29:59]
SELECT decode(a.job,'',b.dname|| '총계',b.dname) , a.job, SUM(a.sal) sal,
COUNT(a.empno) emp_count
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(b.dname, a.job)

한반도님 이렇게 하심..될듯합니다.

by 키티마징가 [2014.05.27 13:57:32]

너무 감사합니다.


by 니똥원샷 [2014.10.12 00:50:26]

감사합니다


by 므흣 [2016.05.02 22:03:49]

하나의 예제에 서로다른 스타일 ....

오라클의 세계란... 참...

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입