[오늘쿼리] (SUM + DECODE)

부서 번호, 부서 번호별 토탈 월급 가로 출력

SELECT SUM(DECODE(DEPTNO, 10, SAL)) AS '10',
	   SUM(DECODE(DEPTNO, 20, SAL)) AS '20',
       SUM(DECODE(DEPTNO, 30, SAL)) AS '30',
	FROM EMP;

출력 결과

10 | 20 | 30
8750 | 10875 | 9400

부서 번호 10번, 월급 출력 아니면 NULL

SELECT DEPTNO, DECODE(DEPTNO, 10, SAL) AS '10'
	FROM EMP;

• DEPTNO '10' 인 것만 월급 출력

부서번호(DEPTNO 컬럼 제외) 부서번호 10번의 월급 합계

SELECT SUM(DECODE(DEPTNO, 10, SAL)) AS '10'
	FROM EMP;

출력결과

10
8750

직업, 직업별 토탈 월급 출력

SELECT SUM(DECODE(JOB, 'ANALYST', SAL)) AS 'ANALYST',
	   SUM(DECODE(JOB, 'CLERK', SAL)) AS 'CLERK',
       SUM(DECODE(JOB, 'MANAGER', SAL)) AS 'MANAGER',
       SUM(DECODE(JOB, 'SALESMAN', SAL)) AS 'SALESMAN',
	FROM EMP;

출력결과

ANALYST | CLERK | MANAGER | SALESMAN
6000 | 4150 | 8275 | 5600

부서번호 별 월급분포

SELECT DEPTNO, SUM(DECODE(JOB, 'ANALYST', SAL)) AS 'ANALYST',
	   SUM(DECODE(JOB, 'CLERK', SAL)) AS 'CLERK',
       SUM(DECODE(JOB, 'MANAGER', SAL)) AS 'MANAGER',
       SUM(DECODE(JOB, 'SALESMAN', SAL)) AS 'SALESMAN',
	FROM EMP
    GROUP BY DEPTNO;

출력결과

DEPTNO | ANALYST | CLERK | MANAGER | SALESMAN
30 | | 950 | 2850 | 5600
10 | | 1300 | 2450 |
20 | 6000 | 1900 | 2975 |