SQL 집계 함수와 GROUP BY - COUNT, SUM, AVG, HAVING 사용법 (SQL 초급 6단원)

이 단원에서 배우는 것

여기까지 다룬 조회는 전부 행 단위였다. 한 행을 걸러 내고(2단원), 줄 세우고(3단원), 고쳤다(5단원). 이번 단원은 여러 행을 하나로 접는다. "사원 목록"이 아니라 "부서별 인원과 평균 연봉" 같은 요약이 필요할 때 쓰는 문법이다. 1단원에서 정리한 처리 순서 중 아직 안 쓴 GROUP BY 와 HAVING 이 여기서 등장하고, 그 순서를 알아야 WHERE 와 HAVING 을 헷갈리지 않는다. 데이터는 1단원의 INSERT 기준이다(5단원 연습문제를 실행했다면 연봉 값이 조금 다를 수 있다).

COUNT·SUM·AVG·MIN·MAX 가 NULL 을 어떻게 다루는지 구분한다.
GROUP BY 로 그룹을 만들고, SELECT 절에 무엇을 쓸 수 있는지 안다.
WHERE 와 HAVING 을 처리 순서로 구분해서 쓴다.

개념

집계 함수는 여러 행을 입력받아 값 하나를 낸다. 여기서 반드시 짚고 갈 규칙이 하나 있다. COUNT(*) 를 뺀 모든 집계 함수는 NULL 을 아예 못 본 것처럼 건너뛴다. 더하지도, 세지도, 평균의 분모에 넣지도 않는다. 이 규칙 하나에서 이 단원의 사고 대부분이 나온다.

GROUP BY 는 "묶는 기준"을 정하는 절이다. GROUP BY dept_id 라고 쓰면 dept_id 값이 같은 행끼리 한 덩어리가 되고, 각 덩어리가 결과의 한 행이 된다. 그래서 SELECT 절에는 두 가지만 올 수 있다. 그룹을 만든 기준 컬럼이거나, 덩어리 전체를 하나로 접는 집계 함수이거나. emp_name 처럼 덩어리 안에서 값이 여러 개인 컬럼은 무엇을 보여줄지 정할 수 없으므로 쓸 수 없다.

WHERE 와 HAVING 의 구분도 처리 순서로 끝난다. WHERE 는 GROUP BY 보다 먼저이므로 그룹을 만들기 전에 행을 걸러낸다. HAVING 은 GROUP BY 보다 뒤이므로 이미 만들어진 그룹을 걸러낸다. 그래서 집계 결과를 조건으로 쓰려면 HAVING 이어야 하고, 개별 행의 값으로 거를 수 있으면 WHERE 여야 한다.

표준 SQL 문법과 예제

COUNT 네 가지를 한 번에 비교한다

SELECT COUNT(*)                AS 전체행,
       COUNT(dept_id)          AS 부서있음,
       COUNT(bonus)            AS 보너스있음,
       COUNT(DISTINCT dept_id) AS 부서수
FROM   emp;

+--------+----------+------------+--------+
| 전체행 | 부서있음 | 보너스있음 | 부서수 |
+--------+----------+------------+--------+
|     10 |        9 |          5 |      4 |
+--------+----------+------------+--------+
1 row in set (0.00 sec)

네 값이 전부 다르다. COUNT(*) 는 행을 센다. COUNT(컬럼) 은 그 컬럼이 NULL 이 아닌 행만 센다. 부서 미배정 사원 1명이 빠져 9, 보너스가 있는 사원만 세면 5 다. COUNT(DISTINCT 컬럼) 은 NULL 을 뺀 서로 다른 값의 개수라 4 다. "전체 건수"를 세려는 자리에 습관적으로 COUNT(id) 를 쓰면 NULL 이 있는 컬럼에서 조용히 틀린다. 행 수는 언제나 COUNT(*) 다.

GROUP BY

SELECT dept_id,
       COUNT(*)    AS cnt,
       AVG(salary) AS avg_sal
FROM   emp
GROUP BY dept_id
ORDER BY dept_id;

+---------+-----+-------------+
| dept_id | cnt | avg_sal     |
+---------+-----+-------------+
|    NULL |   1 | 4400.000000 |
|      10 |   2 | 3450.000000 |
|      20 |   3 | 5733.333333 |
|      30 |   3 | 4933.333333 |
|      40 |   1 | 5000.000000 |
+---------+-----+-------------+
5 rows in set (0.00 sec)

두 가지를 확인한다. 첫째, dept_id 가 NULL 인 사원들도 하나의 그룹으로 묶인다. WHERE 에서는 NULL = NULL 이 참이 아니었지만, GROUP BY 와 DISTINCT 는 NULL 끼리 같은 값으로 취급한다. 둘째, 사원이 없는 총무팀(50)은 결과에 아예 없다. emp 테이블에 그 부서를 가진 행이 한 건도 없으니 그룹 자체가 만들어지지 않는다. 부서 목록 전체를 기준으로 0명까지 보이게 하려면 dept 테이블과 조인해야 하는데, 그건 이 커리큘럼 다음 단계의 주제다.

기준 컬럼을 여러 개 쓰면 조합별로 묶인다.

SELECT dept_id, job, COUNT(*) AS cnt, MAX(salary) AS max_sal
FROM   emp
GROUP BY dept_id, job
ORDER BY dept_id, job;

WHERE 와 HAVING

-- 팀장을 뺀 사원들로 부서별 평균을 내고, 그 평균이 4500 이상인 부서만 본다
SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM   emp
WHERE  job <> '팀장'          -- 그룹을 만들기 전에 행을 걸러낸다
GROUP BY dept_id
HAVING AVG(salary) >= 4500     -- 만들어진 그룹을 걸러낸다
ORDER BY dept_id;

같은 조건을 반대쪽에 쓰면 어떻게 되는지가 핵심이다. HAVING job <> '팀장' 은 GROUP BY 뒤라 job 컬럼이 이미 그룹으로 접혀 사라졌으므로 오류다. WHERE AVG(salary) >= 4500 은 WHERE 시점에 아직 평균이 계산되지 않았으므로 역시 오류다. 처리 순서를 외워 두면 매번 판단할 필요가 없다.

한편 WHERE 로 쓸 수 있는 조건을 HAVING 에 넣는 것은 문법상 되지만 손해다. HAVING dept_id = 20 은 모든 부서를 다 묶은 뒤에 하나만 남기는 셈이라, 처음부터 WHERE dept_id = 20 으로 읽는 양을 줄이는 쪽이 훨씬 빠르다.

DB별 차이

항목	MySQL / MariaDB	PostgreSQL	Oracle	SQL Server
GROUP BY 에 없는 컬럼을 SELECT 에	MySQL 5.7+ 는 기본 차단(`ONLY_FULL_GROUP_BY`). MariaDB 는 기본 허용	오류	오류(ORA-00979)	오류
HAVING 에서 SELECT 별칭 사용	가능(비표준)	불가	불가	불가
GROUP BY 에 별칭·위치번호	가능	가능	불가	불가
문자열 이어 붙이기 집계	`GROUP_CONCAT()`	`STRING_AGG()`	`LISTAGG()`	`STRING_AGG()`

MariaDB 나 ONLY_FULL_GROUP_BY 를 끈 MySQL 에서 SELECT dept_id, emp_name, COUNT(*) FROM emp GROUP BY dept_id 를 돌리면 오류 없이 결과가 나온다. 그런데 emp_name 은 그 그룹에 속한 사원 중 아무나 한 명이다. 어떤 행이 뽑히는지는 보장되지 않으며 실행 계획이 바뀌면 값도 바뀐다. 개발 환경에서 우연히 맞아 보이다가 운영에서 틀리는 전형적인 경로다. 이 설정은 켜 두는 편이 낫다.

실무에서 자주 틀리는 것

1. AVG 의 분모가 생각과 다르다

SELECT AVG(bonus)                    AS avg1,
       SUM(bonus) / COUNT(*)         AS avg2,
       AVG(COALESCE(bonus, 0))       AS avg3
FROM   emp;
-- avg1 = 580.000000, avg2 = 290.000000, avg3 = 290.000000

AVG(bonus) 는 보너스를 받은 5명만 나눈 값이고, 나머지 둘은 전 사원 10명으로 나눈 값이다. 셋 다 맞는 계산이지만 뜻이 다르다. "평균 보너스"라는 말을 들었을 때 보너스 수령자 평균인지 전 사원 평균인지를 먼저 확인해야 한다. 후자라면 NULL 을 0 으로 바꾼 뒤 평균을 내야 한다는 것을 코드에 명시적으로 남긴다.

2. 0행일 때 SUM 이 0 이 아니라 NULL 이다

-- 조건에 맞는 행이 하나도 없을 때
SELECT COUNT(*) AS c, SUM(salary) AS s
FROM   emp
WHERE  dept_id = 999;
-- c = 0, s = NULL

COUNT 는 0 을 돌려주지만 SUM 은 NULL 을 돌려준다. 이 값을 그대로 애플리케이션으로 넘기면 숫자를 기대하던 쪽에서 널 포인터 오류가 나거나, 다시 다른 값과 더해져 결과 전체가 NULL 이 된다. 합계를 화면에 뿌릴 목적이라면 감싸 준다.

SELECT COALESCE(SUM(salary), 0) AS s FROM emp WHERE dept_id = 999;

3. 조건부 집계를 서브쿼리로 여러 번 돈다

"부서별 전체 인원과 그중 개발자 수"를 구하려고 테이블을 두 번 읽는 사람이 많다. 집계 함수 안에 CASE 를 넣으면 한 번에 끝난다. 표준 문법이라 어느 DB 에서나 동작한다.

SELECT dept_id,
       COUNT(*)                                    AS 전체,
       SUM(CASE WHEN job = '개발자' THEN 1 ELSE 0 END) AS 개발자
FROM   emp
GROUP BY dept_id
ORDER BY dept_id;

4. COUNT(*) 가 항상 느리다고 믿는다

SELECT COUNT(*) FROM 큰테이블 은 InnoDB 에서 실제로 전체를 훑는다. 하지만 COUNT(1) 이나 COUNT(id) 로 바꿔도 빨라지지 않는다. 세는 방식이 같기 때문이다. 인터넷에 도는 "COUNT(1) 이 더 빠르다"는 이야기는 근거가 없다. 정말 느리다면 조건에 맞는 인덱스를 만들어 인덱스만 읽게 하거나, 정확한 총건수가 꼭 필요한지부터 다시 묻는 편이 낫다.

스스로 확인하기

부서별 인원수와 평균 연봉을 부서번호 순으로 조회하라. 부서가 없는 사원도 하나의 그룹으로 나와야 한다.
직무(job)별 최고 연봉과 최저 연봉을 구하되, 인원이 2명 이상인 직무만 보여라.
전체 사원 수와 보너스를 받은 사원 수를 한 번의 조회로 구하라.

-- 1
SELECT dept_id, COUNT(*) AS 인원, AVG(salary) AS 평균연봉
FROM   emp
GROUP BY dept_id
ORDER BY dept_id;

-- 2
SELECT job, MAX(salary) AS 최고, MIN(salary) AS 최저, COUNT(*) AS 인원
FROM   emp
GROUP BY job
HAVING COUNT(*) >= 2
ORDER BY job;
-- 개발자 3명, 영업 2명, 인사 2명, 팀장 2명이 남고 재무 1명은 빠진다

-- 3
SELECT COUNT(*)     AS 전체사원,
       COUNT(bonus) AS 보너스수령
FROM   emp;
-- 10, 5. COUNT(bonus) 가 NULL 을 세지 않는 성질을 그대로 쓴 것이다

PostgreSQL 공식 문서 - Aggregate Functions