SQL 그룹함수: 그룹함수의 종류, GOUP BY, HAVING절

그룹 함수

1) 단일행 함수 : 각 행에 대해 함수의 결과가 구해지므로 함수의 적용 전과 동일하게 여러개의 로우로 결과가 나온다.

2) 그룹 함수 : 여러개의 행을 묶어 연산하여 총합, 평균 등 하나의 결과를 출력한다.

1) 단일행 함수의 예

SELECT DEPTNO, ROUND(SAL, 3) FROM EMP;

결과) 여러 행에 대한 결과가 여러 행으로 출력된다.

2) 그룹 함수의 예

SELECT SUM(SAL) FROM EMP;

결과) 여러 행에 대한 결과가 '29025'라는 한 개의 행으로 출력된다.

1. 그룹 함수의 종류

SUM : 그룹의 합계

AVG : 그룹의 평균

COUNT : 그룹의 총 개수

MAX : 그룹의 최대값

MIN : 그룹의 최소값

STDDEV : 그룹의 표준편차

VARIANCE : 그룹의 분산

1) SUM : 그룹의 합계

SELECT SUM(SAL) FROM EMP; -- 급여 SAL 컬럼값들의 합계

SELECT SUM(COMM) FROM EMP; -- 커미션 COMM 컬럼값들의 합계

- 커미션 컬럼에 null 값이 존재하지만 그룹 함수는 null을 제외하고 계산하므로 올바른 결과가 나온다.

2) AVG : 그룹의 평균

SELECT AVG(SAL) FROM EMP; -- SAL 컬럼의 평균을 구한다.

SELECT ROUND(AVG(SAL), 1) FROM EMP;

3) MAX / MIN : 그룹의 최대값과 최소값

SELECT MAX(SAL), MIN(SAL) FROM EMP; 

-- 급여 SAL 컬럼의 최대값과 최소값 출력. 최대급여와 최소급여를 알 수 있다.

최대급여를 받는 사원의 이름도 출력하려면?

SELECT  ENAME, MAX(SAL) FROM EMP; 

이렇게 해야할 것 같지만 에러 발생. 그룹함수는 그룹합수를 적용하지 않은 ENAME 같은 단순컬럼이 올 수 없다.

때문에 최대급여를 받는 사원 이름을 출력하려면 다음과 같이 서브쿼리문으로 처리해야 한다. 

내멋대로 문제) 최대급여를 받는 사원 이름과 급여를 출력하라.

SELECT ENAME, SAL FROM EMP

WHERE SAL = (SELECT MAX(SAL) FROM EMP);

SAL컬럼의 최대값을 서브쿼리로 잡고 메인쿼리에서 ENAME과 SAL을 출력하면 최대급여를 받는 사원 이름 KING과 최대급여인 5000이 출력된다.

문제) 가장 최근 입사한 사원의 입사일과 입사한 지 가장 오래된 사원의 입사일을 출력하는 쿼리문은?

SELECT MAX(HIREDATE) 입사일, MIN(HIREDATE) 입사일 FROM EMP;

문제) 10번 부서 소속 사원 중 커미션을 받는 사원의 수는?

SELECT COUNT(COMM) 사원수 FROM EMP WHERE DEPTNO= 10;

4) COUNT : 그룹의 총 개수

SELECT COUNT(COMM) FROM EMP; -- 조건을 만족하는 로우의 개수를 출력

- COUNT 함수는 그룹함수이므로 null을 제외한 값을 출력한다.

- null값을 포함한 테이블 전체의 로우 수를 구하려면 COUNT(*)를 써야 한다.

SELECT COUNT(*) FROM EMP;

- 테이블 전체의 로우(행) 개수 출력

SELECT COUNT(JOB) FROM EMP; -- 중복되는 것 포함한 총 로우 카운트

SELECT COUNT(DISTINCT JOB) FROM EMP; -- 중복 제외하고 출력

2. GROUP BY절

어떤 컬럼 값을 기준으로 그룹함수를 적용할 지 지정

SELECT 컬럼명, 그룹함수

FROM 테이블명

WHERE 조건 (연산자)

GROUP BY 컬럼명;

SELECT DEPTNO FROM EMP

GROUP BY DEPTNO; -- 부서번호로 사원을 그룹지어 부서번호 출력

SELECT DEPTNO, SUM(SAL) FROM EMP

GROUP BY DEPTNO; 

- 부서번호를 기준으로 합계를 구한다. 어느 부서가 급여를 많이 받는지 알 수 있다.

SELECT DEPTNO, COUNT(*), COUNT(COMM) FROM EMP

GROUP BY DEPTNO;

- 각 부서별로 부서 전체인원수, 부서 전체인원 중 커미션을 받는 인원수를 구할 수 있다.

SELECT DEPTNO, FLOOR(AVG(SAL)) FROM EMP -- 소수점 절삭 FLOOR

GROUP BY DEPTNO;

- EMP에서 각 부서번호와 그 부서의 평균 급여가 출력된다.

위의 예문에 부서명 DNAME을 넣어보자. 하지만 DNAME은 GROUP BY절에 명시가 안되었으므로 에러가 난다. 

SELECT DEPTNO, DNAME, AVG(SAL) FROM EMP

GROUP BY DEPTNO;

GROUP BY절로 묶이지 않은 단순 컬럼은 SELECT절에 사용할 수 없기 때문이다.

내멋대로 문제)

위의 SELECT문에 DNAME(부서명)을 넣으려면 어떻게 해야할까? (JOIN과 서브쿼리를 사용)

위의 예문 부서번호, 부서 평균급여는 EMP, 부서명은 DEPT에 있으니 두 테이블의 JOIN이 필요하다.

- FROM절에서 부서테이블 DEPT는 DEPT01, 사원테이블 EMP는 DEPT02로 별칭(ALIAS)를 붙이자.

- SELECT절에는 DEPT01.DEPTNO(부서번호), DEPT01.DNAME(부서명), DEPT02.SAL(급여평균)을 쓰자.

- WHERE에는 DEPT01.DEPTNO = DEPT02.DEPTNO를 넣어둔다.

SELECT DEPT01.DEPTNO, DEPT01.DNAME, DEPT02.SAL

FROM DEPT DEPT01, EMP DEPT02

WHERE DEPT01.DEPTNO = DEPT02.DEPTNO;

하지만 이것만으로는 계산이 되지 않는다. 위의 예문에 AVG(SAL)을 어떻게 DEPT02.SAL(급여평균)에 넣을 수 있을까?

정답은 FROM 항목에서 EMP 항목에 괄호를 넣어 서브쿼리로 위의 평균값을 구하는 예문을 넣자.

SELECT DEPT01.DEPTNO, DEPT01.DNAME, DEPT02.SAL

FROM DEPT DEPT01, 

(SELECT DEPTNO, FLOOR(AVG(SAL)) SAL FROM EMP GROUP BY DEPTNO) DEPT02

WHERE DEPT01.DEPTNO = DEPT02.DEPTNO;

무사히 부서번호, 부서명, 평균값이 출력된다.

주의할 점은 FLOOR(AVG(SAL)) 뒤에 SAL이라는 ALIAS(별칭)를 붙여 위에서 DEPT02.SAL로 가져와서 쓸 수 있었다는 것. 원래는 AS SAL인데 AS는 생략할 수 있다. 

SELECT DEPT01.DEPTNO, DEPT01.DNAME, DEPT02.AVGSAL

FROM DEPT DEPT01, 

(SELECT DEPTNO, FLOOR(AVG(SAL)) AS AVGSAL FROM EMP

GROUP BY DEPTNO) DEPT02

WHERE DEPT01.DEPTNO = DEPT02.DEPTNO;

FLOOR(AVG(SAL))를 SAL이라는 AIias 대신에 as AVGSAL로 바꾸면 위와 같다.

3. HAVING 조건

SELECT문에서는 WHERE절을 사용하여 결과를 제한하는데, 그룹 결과를 제한할 때에는 HAVING절을 사용한다.

그룹함수를 적용한 컬럼이 조건. 그룹함수를 적용해서 나온 결과값 중 원하는 조건에 부합하는 자료만 산출할 때 쓴다.

SELECT DEPTNO, SUM(SAL) FROM EMP

GROUP BY DEPTNO

HAVING SUM(SAL) >= 8750; -- 부서 총급여가 8750 이상인 부서만 표시