SQL 그룹함수, Group by, Having 조건식

그룹함수

여러개의 행에 대해 하나의 행으로 결과를 반환하는 함수,
통계에 관련된 함수 : 최대값, 최소값, 평균, 총레코드수, 합계 등

함수	설명
count()	해당 필드의 레코드 수를 반환하는 함수, 각 열마다 값이 있으면 1, 없으면 0을 반환 count( * ) : null을 포함한 필드의 레코드 수(열 수), 주로 사용됨 or count(필드명) : null을 포함하지 않음
min()	필드의 최소값을 반환하는 함수
max()	필드의 최대값을 반환하는 함수
avg()	필드의 평균을 반환하는 함수
sum()	필드의 합계를 반환하는 함수
....	통계에 더욱 깊은 관련이 있는 함수들도 있음 분산, 표준편차 등

그룹함수 사용례

{ count() }

▷ emp테이블에서 급여가 3000이상인 사원의 수 구하기

▶ [ SQL> select count(*) from emp where sal>=3000; ]

count( * )
3

{ avg() max() min() sum() }

▷ emp테이블에서 필드의 총 레코드수, 급여의 평균(소수점 탈락), 최대값, 최소값을 구하고 급여의 총합계 앞에
달러기호($)를 붙인 뒤 3자리마다 콤마(,)를 표기

▶ [ SQL> select count( * ), trunc(avg(sal)), max(sal), min(sal), to_char(sum(sal), '$999,999') from emp; ]

COUNT( * )	TRUNC(AVG(SAL))	MAX(SAL)	MIN(SAL)	TO_CHAR~
14	2073	5000	800	$29,025

※ Min(), Max()_문자, 날짜 데이터 계산 ※

▷ emp테이블에서 이름, 날짜, 급여의 최소/최대값 조회

▶ [ SQL> select min(ename), max(ename), min(hiredate), max(hiredate), min(sal), max(sal) from emp; ]

MIN(ENAME)	MAX(ENAME)	MIN(HIREDATE)	MAX(HIREDATE)	MIN(SAL)	MAX(SAL)
ADAMS	WARD	80/12/17	87/05/23	800	5000

※ 알파벳은 A ~ Z순으로 크기를 계산할 수 있고,
날짜는 현재에 가까울수록 큰 값으로 처리됨

Group by 

# 예제_에러

▶ emp테이블에서 이름, 필드의 총 레코드수, 급여의 평균(소수점 탈락), 최대값, 최소값을 구하고 급여의 총합계 앞에 달러기호($)를 붙인 뒤 3자리마다 콤마(,)를 표기
[ SQL> select ename, count( * ), trunc(avg(sal)), max(sal), min(sal), to_char(sum(sal), '$999,999') from emp;
→ ORA-00937: 단일 그룹의 그룹 함수가 아닙니다 ]

※ select ~ from 사이에 그룹함수는 일반 필드와 같이 사용할 수 없음
ename : 일반필드, count( * ) ~ : 그룹함수

∴ 일반필드를 그룹함수와 성격이 갖도록 설정해야함

§ 형식

[ SQL> select 필드,,, 그룹함수,,,
from 테이블명
where 조건식
group by 그룹을 지워줄 필드명
order by 필드명 ; ]

# 풀이

▶ [ SQL> select ename, count( * ), trunc(avg(sal)), max(sal), min(sal), to_char(sum(sal), '$999,999')
from emp group by ename;

ENAME	COUNT( * )	TRUNC(AVG(SAL))	MAX(SAL)	MIN(SAL)	TO_CHAR~
ALLEN	1	1600	1600	1600	$1,600
JONES	1	2975	2975	2975	$2,975
...	...	...	...	...	..

※ 그룹함수가 각각의 레코드에 적용된 값이 표출되기 때문에 원래의 sal값과 차이가 없음

그룹함수 중첩

• max(avg(~)) : 평균의 최대값을 산출

• max(sum(~)) : 합계의 최대값 산출

• min(sum(~)) : 합계의 최소값 산출

  ※ 그룹함수는 한 번만 중첩이 가능함

# 예제

▷ emp테이블에서 부서번호별로 급여 평균의 최대값, 급여합계의 최대값, 급여합계의 최소값, 급여가 가장 높은 사람의 급여를 출력

▶ [ SQL> select max(avg(sal)), max(sum(sal)), min(sum(sal)), max(max(sal))
from emp
group by deptno; ]
※ 그룹함수 중첩시에는 group by에 일반필드를 선언해도
일반필드와 그룹함수의 결과를 동시에 표출할 수 없음

MAX(AVG(SAL))	MAX(SUM(SAL))	MIN(SUM(SAL))	MAX(MAX(SAL))
2916.6667	10875	8750	5000

Having 조건식

그룹함수 조건식
그룹함수를 이용해서 조건식을 작성하는 경우에 필요함

# 에러

▷ emp테이블에서 부서별로 사원의 수가 4명이상인 부서의 사원 정보를 찾아서
부서번호, 급여의 합계를 출력

▶ [ SQL> select deptno, sum(sal) as 급여합계 from emp
where count( * ) >= 4
group by deptno;
1행에 오류: ORA-00934: 그룹 함수는 허가되지 않습니다 ]

※ where 조건식은 일반적인 검색을 할 때 사용, 그룹 함수 사용 불가 ※

§ 형식

[ SQL> select ~ from ~ where ~ group by ~ having 조건식 order by ~ ]

# 풀이

▷ emp테이블에서 사원의 수가 4명 이상인 부서의 급여합계를 구하고,
급여합계가 높은 순으로 정렬

▶ [ SQL> select deptno, sum(sal) as 급여합계 from emp
group by deptno
having count( * ) >= 4
order by sum(sal) desc; ]

DEPTNO	급여합계
20	10875
30	9400

# 예제

▷ emp테이블에서 업무가 MANAGER가 아닌 사원들에 대해
부서별로 급여의 합이 2500이상인 부서의 정보를 출력
부서번호, 급여 합계순으로 출력, 급여 합계가 높은 순으로 정렬

▶ [ SQL> select deptno, sum(sal) "부서별 급여합계" from emp
where job !='MANAGER'
group by deptno
having sum(sal) >= 2500
order by 2 desc; ]

DEPTNO	부서별 급여합계
20	7900
30	6550
10	6300

---

그룹함수에는 group by나 having과 같이 일반필드와는 다른 구문이나 조건들이 필요하기 때문에 따로 익혀두는 것이 필요합니다.