SQL Fundamental5 (Group by, With)

Group by

만약 다음과 같은 테이블이 있다 한다.

직원들의 부서별 급여의 합을 구하려면 Group by 기능을 사용한다.

select deptno, sum(sal) as sum_salary from hr.emp
group by deptno

• Group by 절에 기술된 컬럼 값(또는 가공 컬럼값)으로 그룹화 한 뒤 집계(Aggregation) 함수와 함께 사용되어 그룹화된 집계 정보를 제공한다.
• Group by 절에 기술된 컬럼 값으로 반드시 1의 집합을 가지게 됨. (중복된 value가 없음)
• Select 절에는 Group by 절에 기술된 컬럼(또는 가공 컬럼)과 집계 함수만 사용될 수 있음.

만약 그룹화한 결과에 조건을 걸고 싶다면 having을 사용한다.

SELECT job
     , SUM(sal) AS sum_sal
  FROM emp
 WHERE deptno IN (20, 30)
 GROUP BY job HAVING SUM(sal) > 5000

having이랑 where 둘 다 조건에 따른 필터링 기능을 가지고 있다. 하지만 둘의 차이점은 where는 그룹화 하기 전이고, having은 그룹화 후에 조건입니다.

emp 테이블

emp 테이블에서 부서별 최대 급여, 최소 급여, 평균 급여를 구할것.

select deptno, max(sal), min(sal), round(avg(sal), 2) as avg_sal 
from emp
group by deptno 
order by deptno asc

결과

emp 테이블에서 부서별 최대 급여, 최소 급여, 평균 급여를 구하되 평균 급여가 2000 이상인 경우만 추출.

select deptno, max(sal), min(sal), round(avg(sal), 2)
from emp
group by deptno
having avg(sal) >= 2000
order by deptno asc

부서명 SALES와 RESEARCH 소속 직원별로 과거부터 현재까지 모든 급여를 취합한 평균 급여

-- 부서명 SALES와 RESEARCH 소속 직원별로 과거부터 현재까지 모든 급여를 취합한 평균 급여

select a.empno, max(a.ename ), max(c.dname), round(avg(b.sal), 2) 
from emp a
	join emp_salary_hist b on a.empno = b.empno 
	join dept c on a.deptno = c.deptno
where c.dname in ('SALES', 'RESEARCH')
group by a.empno

위의 코드를 with 절로 사용하기.

with 
temp_01 as 
(
select a.empno, max(a.ename) ename, max(c.dname) dname, round(avg(b.sal), 2) avg_sal
from emp a
	join emp_salary_hist b on a.empno = b.empno 
	join dept c on a.deptno = c.deptno
where c.dname in ('SALES', 'RESEARCH')
group by a.empno
)
select * from temp_01

with 절은 서브 쿼리를 만드는 문법이다. 임시 테이블을 만들어 가독성을 더욱 높이는 기능이다.