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과 같이 일반필드와는 다른 구문이나 조건들이 필요하기 때문에 따로 익혀두는 것이 필요합니다.