DB - UNION ALL(직업별 월급 구해보기)

직업별 월급 평균 구하기 

UNION ALL 은 합쳐주는 역할을 한다. 

 

JOB이 같은 값들끼리 sal의 평균을 출력해주는 코드(필요 하지 않는 계산들은 모두 null값 처리 해주었다.)

SELECT job, sal, ename, hiredate, mgr, comm, deptno, empno
FROM emp
WHERE job = 'ANALYST'

UNION ALL

SELECT '소계', round(avg(sal), 2), null, null, null, null, null, null 
FROM emp
WHERE job = 'ANALYST'

UNION ALL

SELECT job, sal, ename, hiredate, mgr, comm, deptno, empno
FROM emp
WHERE job = 'CLERK'

UNION ALL

SELECT '소계', round(avg(sal), 2), null, null, null, null, null, null 
FROM emp
WHERE job = 'CLERK'

UNION ALL

SELECT job, sal, ename, hiredate, mgr, comm, deptno, empno
FROM emp
WHERE job = 'MANAGER'

UNION ALL

SELECT '소계', round(avg(sal), 2), null, null, null, null, null, null 
FROM emp
WHERE job = 'MANAGER'

UNION ALL

SELECT job, sal, ename, hiredate, mgr, comm, deptno, empno
FROM emp
WHERE job = 'SALESMAN'

UNION ALL

SELECT '소계', round(avg(sal), 2), null, null, null, null, null, null 
FROM emp
WHERE job = 'SALESMAN'

UNION ALL

SELECT '총계', round(avg(sal), 2), null, null, null, null, null, null 
FROM emp;

처음이라 알아보기 쉽게 UNION ALL 기준으로 공백을 주었다 

결과 값