[실습] 부서 & 직함별로 급여 합, 보너스 합, 인원 수 출력
|
WITH EMP AS
(
SELECT 10 AS DEPTNO, 'CLERK' AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200 AS BONUS FROM DUAL UNION ALL
SELECT 10 AS DEPTNO, 'MANAGER' AS JOB, 7782 AS EMPNO, 'CLARK' AS ENAME, 2450 AS SAL, 100 AS BONUS FROM DUAL UNION ALL
SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING' AS ENAME, 5000 AS SAL, 0 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'ANALYST' AS JOB, 7902 AS EMPNO, 'FORD' AS ENAME, 3000 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'ANALYST' AS JOB, 7788 AS EMPNO, 'SCOTT' AS ENAME, 3000 AS SAL, 600 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'CLERK' AS JOB, 7876 AS EMPNO, 'ADAMS' AS ENAME, 1100 AS SAL, 200 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'CLERK' AS JOB, 7369 AS EMPNO, 'SMITH' AS ENAME, 800 AS SAL, 200 AS BONUS FROM DUAL UNION ALL
SELECT 20 AS DEPTNO, 'MANAGER' AS JOB, 7566 AS EMPNO, 'JONES' AS ENAME, 2975 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'CLERK' AS JOB, 7900 AS EMPNO, 'JAMES' AS ENAME, 950 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'MANAGER' AS JOB, 7698 AS EMPNO, 'BLAKE' AS ENAME, 2850 AS SAL, 400 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7521 AS EMPNO, 'WARD' AS ENAME, 1250 AS SAL, 500 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7499 AS EMPNO, 'ALLEN' AS ENAME, 1600 AS SAL, 300 AS BONUS FROM DUAL UNION ALL
SELECT 30 AS DEPTNO, 'SALESMAN' AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS FROM DUAL
)
SELECT DEPTNO,
JOB,
SUM(SAL) AS SUM_SAL, /* (부서 & 직함별) 급여 합 */
SUM(CASE WHEN JOB = 'ANALYST' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_ANALYST,
SUM(CASE WHEN JOB = 'CLERK' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_CLERK,
SUM(CASE WHEN JOB = 'MANAGER' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_MANAGER,
SUM(CASE WHEN JOB = 'SALESMAN' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_SALESMAN,
SUM(CASE WHEN JOB = 'PRESIDENT' /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
THEN SAL
END) AS SUM_SAL_PRESIDENT,
SUM(BONUS) AS SUM_BONUS, /* (부서 & 직함별) 보너스 합 */
COUNT(*) AS CNT /* (부서 & 직함별) 인원 수 */
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB
CASE 문을 DECODE로 변경 (CASE문 방식과 결과는 동일) |
WITH EMP AS
(
...
...
...
)
SELECT DEPTNO,
JOB,
SUM(SAL) AS SUM_SAL, /* (부서 & 직함별) 급여 합 - NON-PIVOT */
SUM(DECODE(JOB, 'ANALYST', SAL)) AS SUM_SAL_ANALYST, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'CLERK', SAL)) AS SUM_SAL_CLERK, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'MANAGER', SAL)) AS SUM_SAL_MANAGER, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'SALESMAN', SAL)) AS SUM_SAL_SALESMAN, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(DECODE(JOB, 'PRESIDENT', SAL)) AS SUM_SAL_PRESIDENT, /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
SUM(BONUS) AS SUM_BONUS, /* (부서 & 직함별) 보너스 합 - NON-PIVOT */
COUNT(*) AS CNT /* (부서 & 직함별) 인원 수 - NON-PIVOT */
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB
GROUP BY 조건을 변경(JOB을 그룹핑 제외)하여 쿼리 수행 |
WITH EMP AS
(
...
...
...
)
SELECT DEPTNO,
-- JOB, /* JOB을 그룹핑 제외 */
SUM(SAL) AS SUM_SAL,
SUM(CASE WHEN JOB = 'ANALYST'
THEN SAL
END) AS SUM_SAL_ANALYST,
...
...
...
SUM(BONUS) AS SUM_BONUS,
COUNT(*) AS CNT
FROM EMP
GROUP BY DEPTNO --, JOB /* JOB을 그룹핑 제외 */
ORDER BY DEPTNO --, JOB /* JOB을 그룹핑 제외 */
|
참조 |
'DB' 카테고리의 다른 글
| [DB, 정규식] PostgreSQL - '[코드]품명' 문자열에서 코드 추출하기 (0) | 2022.03.09 |
|---|---|
| [Oracle] 행 => 열 변환 PIVOT (PIVOT 사용) (0) | 2020.11.01 |
| [Oracle] 「다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL」GROUPING SETS()함수 실습 (0) | 2020.10.31 |
| [Oracle] SQL의 컨셉 for ORACLE 실습용 모델 ERD (0) | 2020.10.27 |
| [Oracle] 계층형 쿼리 (feat. ASL 시즌 9 경기결과) (0) | 2020.10.10 |
