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 ---->> 총계