: 소계, 합계를 같이 구할 때 ROLLUP 사용한다.
a. 전체 합
SELECT SUM(salary)
FROM employees
WHERE department_id < 50;
SUM(SALARY)
-----------
54800
b. 부서별/직급별 합
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY department_id, job_id;
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
30 PU_MAN 11000
30 PU_CLERK 13900
40 HR_REP 6500
c. 부서별 합
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 4400
20 19000
30 24900
40 6500
d. a,b,c 의 결과를 union all한 결과와 비교
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
10 4400 -->> DEPARTMENT_ID별 SUM
20 MK_MAN 13000
20 MK_REP 6000 -->> DEPARTMENT_ID별 SUM
20 19000
30 PU_MAN 11000
30 PU_CLERK 13900
30 24900 -->> DEPARTMENT_ID별 SUM
40 HR_REP 6500
40 6500 -->> DEPARTMENT_ID별 SUM
54800 -->> DEPARTMENT_ID별 총계
2. 컬럼의 구분은 어떻게 ?
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
DEPTNO JOB SUM(SAL)
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 ---->> DEPTNO별 SUM
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875 ---->> DEPTNO별 SUM
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 ---->> DEPTNO별 SUM
29025 ---->> 총계
소계, 합계를 구분하고자 할때 GROUPING(컬럼) 또는 GROUPING_ID(컬럼1, 컬럼2)
를 사용한다.GROUPING() 함수는 해당 컬럼이 집합에 참여하지 않으면 1을 반환, 집합계산에 참여하면 0을 반환
SELECT DEPTNO, JOB, SUM(SAL),
GROUPING(DEPTNO) A,
GROUPING(JOB) B,
GROUPING_ID(DEPTNO,JOB) C
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
DEPTNO JOB SUM(SAL) A B C
10 CLERK 1300 0 0 0
10 MANAGER 2450 0 0 0
10 PRESIDENT 5000 0 0 0
10 8750 0 1 1 ---->> DEPTNO별 SUM
20 CLERK 1900 0 0 0
20 ANALYST 6000 0 0 0
20 MANAGER 2975 0 0 0
20 10875 0 1 1 ---->> DEPTNO별 SUM
30 CLERK 950 0 0 0
30 MANAGER 2850 0 0 0
30 SALESMAN 5600 0 0 0
30 9400 0 1 1 ---->> DEPTNO별 SUM
29025 1 1 3 ---->> 총계
요다할아범