개발자가 되는 방법

GROUP FUNCTIONS : ROLLUP, CUBE, GROUPING SETS

STEP 1. 일반적인 GROUP BY 절 사용

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB;

STEP 1-2. GROUP BY 절 + ORDER BY 절 사용

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB
ORDER BY DNAME, JOB;

STEP 2. ROLLUP 함수 사용

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);

STEP 2-2. ROLLUP 함수 + ORDER BY 절 사용

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB)
ORDER BY DNAME, JOB;

STEP 3. GROUPING 함수 사용

SELECT DNAME, GROUPING(DNAME), JOB, GROUPING(JOB), 
       COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);

STEP 4. GROUPING 함수 + CASE 사용

SELECT CASE GROUPING(DNAME) 
          WHEN 1 THEN 'All Depertments'
          ELSE DNAME
       END AS DNAME, -- DECODE(GROUPING(DNAME), 1, 'All Depertments') AS DNAME
       CASE GROUPING(JOB)
          WHEN 1 THEN 'All Jobs'
          ELSE JOB
       END AS JOB,
       COUNT(*) "Total Empl",
       SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);

STEP 4-2. ROLLUP 함수 일부 사용

SELECT DNAME, 
       CASE GROUPING(JOB)
          WHEN 1 THEN 'All Jobs'
          ELSE JOB
       END AS JOB,
       COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, ROLLUP( JOB);

STEP 4-3. ROLLUP 함수 결합 칼럼 사용

SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, (JOB, MGR));

STEP 5. CUBE 함수

SELECT CASE GROUPING(DNAME) 
          WHEN 1 THEN 'All Depertments'
          ELSE DNAME
       END AS DNAME, -- DECODE(GROUPING(DNAME), 1, 'All Depertments') AS DNAME
       CASE GROUPING(JOB)
          WHEN 1 THEN 'All Jobs'
          ELSE JOB
       END AS JOB,
       COUNT(*) "Total Empl",
       SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE(DNAME, JOB);

STEP 5-2. UNION ALL 사용 SQL

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB

UNION ALL

SELECT DNAME, 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME

UNION ALL

SELECT 'All Departments', JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB

UNION ALL

SELECT 'All Departments', 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO;

STEP 6. GROUPING SETS 함수 : 표시된 인수들에 대한 개별 집계

STEP 6-1. 일반 그룹함수를 이용한 SQL

SELECT DNAME, 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME

UNION ALL

SELECT 'All Departments', JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB

STEP 6-2. GROUPING SETS 사용 SQL

SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
       DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,
       COUNT(*) "Total Empl",
       SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (DNAME, JOB);

STEP 6-3. GROUPING SETS 사용 SQL - 순서 변경

SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
       DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,
       COUNT(*) "Total Empl",
       SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (JOB, DNAME);

STEP 6-4. 3개의 인수를 이용한 GROUPING SETS 이용

SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS ((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR));