SQL 완전 정리 ③ 집계·GROUP BY·HAVING
앞의 두 편은 행 하나하나를 걸러 내고(WHERE) 골라 보여 주는(SELECT) 법이었다. 이 편부터는 여러 행을 하나의 값으로 접는 법을 다룬다. 집계 함수는 "행 여러 개 → 값 하나", GROUP BY 는 "그 접기를 그룹마다 따로", HAVING 은 "접은 결과를 다시 거르기"다. 세 가지가 각각 논리적 처리 순서의 어디에 끼어드는지만 잡으면 이 단원의 함정(별칭, WHERE 의 집계, NULL 그룹, 빈 집합)은 전부 같은 원리로 풀린다. 본문의 모든 쿼리와 결과 표는 SQLite 와 PostgreSQL 에서 실제로 실행해 맞췄고, ROLLUP·CUBE·GROUPING SETS 는 PostgreSQL 로 검증했다.
0. 이 편에서 쓰는 테이블
사원
| eno | ename | dno | job | sal | mgr | hiredate | bonus |
|---|---|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 팀장 | 500 | NULL | 2019-03-01 | NULL |
| 2 | 이도윤 | 10 | 대리 | 350 | 1 | 2021-07-15 | 30 |
| 3 | 박서준 | 10 | 사원 | 280 | 2 | 2023-01-02 | NULL |
| 4 | 최지우 | 20 | 팀장 | 450 | NULL | 2018-11-20 | 50 |
| 5 | 정민재 | 20 | 사원 | 300 | 4 | 2022-05-09 | 20 |
| 6 | 한유나 | 20 | 사원 | 300 | 4 | 2024-02-01 | NULL |
| 7 | 오세린 | NULL | 사원 | 250 | NULL | 2025-01-06 | NULL |
참여
| eno | pno | hours |
|---|---|---|
| 1 | 100 | 20 |
| 2 | 100 | 40 |
| 3 | 100 | 30 |
| 2 | 200 | 10 |
| 5 | 200 | 25 |
눈여겨볼 곳: bonus 는 7명 중 3명만 값이 있고, dno 는 오세린 한 명이 NULL(미배정)이다.
이 두 NULL 이 이 편 함정의 절반을 만든다.
1. 논리적 처리 순서 — 이 편 전체의 지도
SELECT 문은 적힌 순서대로가 아니라 아래 순서로 논리적으로 처리된다(실제 엔진은 최적화하지만 결과는 이 순서로 처리한 것과 같아야 한다).
| 단계 | 입력 | 하는 일 | 이 단계에서 아직 없는 것 |
|---|---|---|---|
| WHERE | 원본 행 | 행 단위 필터 | 그룹, 집계값, SELECT 별칭 |
| GROUP BY | 살아남은 행 | 같은 키끼리 묶음 → 그룹 1개 = 결과 1행 | SELECT 별칭 |
| HAVING | 그룹 | 그룹 단위 필터(집계 조건) | SELECT 별칭(표준) |
| SELECT | 그룹 | 그룹 키·집계로 출력 열 계산, 별칭 탄생 | — |
| ORDER BY | 결과 행 | 정렬 | — (별칭 사용 가능) |
이 표 하나로 다음이 모두 설명된다.
- WHERE 에
COUNT(*)를 못 쓰는 이유 → WHERE 시점엔 그룹이 아직 없다(§7). - HAVING 에 SELECT 별칭을 못 쓰는 이유(표준) → HAVING 이 SELECT 보다 먼저다(§8).
- ORDER BY 에서는 별칭도, 집계도 쓸 수 있는 이유 → 맨 마지막이다(§10).
2. 집계 함수 — 행 여러 개를 값 하나로
2.1 문법 골격
집계함수( [DISTINCT | ALL] 식 )
COUNT(*)
ALL이 기본값이라 보통 생략한다.DISTINCT를 붙이면 중복을 제거한 뒤 집계한다.- GROUP BY 가 없으면 테이블 전체(WHERE 를 통과한 행 전체)가 하나의 그룹이 되어 결과는 항상 1행.
| 함수 | 의미 | NULL 처리 | 대상 타입 |
|---|---|---|---|
COUNT(*) | 행의 수 | 행을 센다 → NULL 도 포함 | — |
COUNT(식) | 식이 NULL 이 아닌 행의 수 | 제외 | 모든 타입 |
COUNT(DISTINCT 식) | 서로 다른 값의 수 | 제외 + 중복 제거 | 모든 타입 |
SUM(식) | 합계 | 제외 | 숫자 |
AVG(식) | 평균 = SUM ÷ (NULL 아닌 개수) | 제외 | 숫자 |
MAX(식) / MIN(식) | 최댓값 / 최솟값 | 제외 | 숫자·문자·날짜 |
핵심 한 줄 — 집계 함수는
COUNT(*)를 제외하고 전부 NULL 을 무시한다. "NULL 을 0 으로 본다"가 아니라 "그 행이 없는 것처럼 본다"이다. 이 차이가 AVG 에서 결과를 바꾼다(§2.3).
2.2 한 번에 비교하기
SELECT COUNT(*), COUNT(bonus), COUNT(DISTINCT sal), COUNT(DISTINCT dno),
SUM(bonus), AVG(bonus), MAX(bonus), MIN(bonus)
FROM 사원;
| COUNT(*) | COUNT(bonus) | COUNT(DISTINCT sal) | COUNT(DISTINCT dno) | SUM(bonus) | AVG(bonus) | MAX(bonus) | MIN(bonus) |
|---|---|---|---|---|---|---|---|
| 7 | 3 | 6 | 2 | 100 | 33.33… | 50 | 20 |
한 칸씩 풀어 보면:
| 결과 | 계산 과정 |
|---|---|
COUNT(*) = 7 | 행이 7개 |
COUNT(bonus) = 3 | 30, 50, 20 만 값. NULL 4개 제외 |
COUNT(DISTINCT sal) = 6 | 500, 350, 280, 450, 300, 300, 250 → 300 중복 접기 → 6 |
COUNT(DISTINCT dno) = 2 | 10, 20 (NULL 은 "서로 다른 값"으로도 세지 않는다) |
SUM(bonus) = 100 | 30 + 50 + 20 |
AVG(bonus) = 33.33… | 100 ÷ 3 (7 이 아니다) |
MAX/MIN 은 숫자만이 아니라 문자·날짜에도 된다. 문자는 정렬 순서(대개 코드값 순), 날짜는 시간 순.
SELECT SUM(sal), AVG(sal), MAX(ename), MIN(hiredate), MAX(hiredate) FROM 사원;
| SUM(sal) | AVG(sal) | MAX(ename) | MIN(hiredate) | MAX(hiredate) |
|---|---|---|---|---|
| 2430 | 347.14… | 한유나 | 2018-11-20 | 2025-01-06 |
MAX(ename) 이 '한유나'인 것은 한글 코드값이 김 < 박 < 오 < 이 < 정 < 최 < 한 순이기 때문이다
(데이터베이스의 정렬 규칙 collation 에 따라 달라질 수 있다).
2.3 AVG 가 NULL 을 빼고 나누는 효과
"보너스 평균"을 묻는 질문은 사실 두 가지 뜻일 수 있다.
SELECT AVG(bonus) AS 받는사람_평균,
SUM(bonus) * 1.0 / COUNT(*) AS 전체_평균,
AVG(COALESCE(bonus, 0)) AS 전체_평균2
FROM 사원;
| 받는사람_평균 | 전체_평균 | 전체_평균2 |
|---|---|---|
| 33.33… | 14.28… | 14.28… |
AVG(bonus)= 100 ÷ 3 → 보너스를 받는 사람들끼리의 평균.AVG(COALESCE(bonus, 0))= 100 ÷ 7 → NULL 을 0 으로 바꿔 분모에 넣은 전 직원 평균.- 그래서
AVG(col)≠SUM(col) / COUNT(*)이고,AVG(col)=SUM(col) / COUNT(col)이다.
함정 — "NULL 이 있으면 평균이 작아진다"고 착각하기 쉬운데 반대다. NULL 을 분모에서 빼기 때문에, NULL 이 있는 열의 AVG 는 0 으로 채운 평균보다 크거나 같다(값이 모두 0 이상일 때). 시험에서 "NULL 을 0 으로 간주"라는 단서가 있으면 COALESCE(Oracle 은 NVL) 로 바꾼 쪽이 답이다.
2.4 빈 집합에서의 결과 — COUNT 는 0, 나머지는 NULL
조건에 맞는 행이 하나도 없을 때 집계 함수는 무엇을 돌려줄까?
SELECT COUNT(*), COUNT(sal), SUM(sal), AVG(sal), MAX(sal), MIN(sal)
FROM 사원
WHERE dno = 99;
| COUNT(*) | COUNT(sal) | SUM(sal) | AVG(sal) | MAX(sal) | MIN(sal) |
|---|---|---|---|---|---|
| 0 | 0 | NULL | NULL | NULL | NULL |
- 결과는 1행이다(GROUP BY 가 없으면 빈 집합도 "전체 그룹 1개"로 본다).
COUNT는 "센 개수"라 0 이 자연스럽다.SUM은 "더할 값이 없음"을 0 이 아니라 NULL 로 표현한다. 표준 SQL 의 정의가 그렇고, Oracle·MySQL·PostgreSQL·SQLite 모두 같다.- 합계를 0 으로 보이고 싶으면
COALESCE(SUM(sal), 0).
그런데 GROUP BY 를 붙이면 이야기가 달라진다.
SELECT dno, COUNT(*) FROM 사원 WHERE dno = 99 GROUP BY dno;
| dno | COUNT(*) |
|---|---|
| (0행) |
GROUP BY 가 있으면 "그룹 1개 = 결과 1행"인데 만들어진 그룹이 0개이므로 결과도 0행이다. COUNT 가 0 인 행조차 나오지 않는다.
| 상황 | 결과 |
|---|---|
| GROUP BY 없음 + 행 0개 | 1행 (COUNT=0, SUM/AVG/MAX/MIN=NULL) |
| GROUP BY 있음 + 행 0개 | 0행 |
2.5 정수 AVG 와 나눗셈 — 엔진 차이
sal 은 정수 열이다. 10번 부서 평균은 (500 + 350 + 280) ÷ 3 = 376.666… 인데 엔진마다 표시가 다르다.
| 엔진 | AVG(정수열) 결과 타입 | 10번 부서 AVG(sal) | 7 / 2 |
|---|---|---|---|
| Oracle | NUMBER | 376.666666… | 3.5 |
| MySQL | DECIMAL | 376.6667 | 3.5000 |
| PostgreSQL | numeric | 376.6666666666666667 | 3 |
| SQLite | REAL(실수) | 376.666666666667 | 3 |
| SQL Server | INT | 376 (소수 버림) | 3 |
- 실행 검증: PostgreSQL 은
AVG(sal)의 타입이numeric,SUM(sal)은bigint,7/2는 3 이었다. SQLite 도7/2= 3,7.0/2= 3.5. - 정보처리기사 실기는 특정 엔진을 정하지 않으므로 보통 수학적 평균(376.67 처럼)으로 답하게 낸다. "정수 나눗셈" 단서가 문제에 있을 때만 버림을 의심하면 된다.
- 직접 나눌 때 소수가 필요하면 한쪽을 실수로:
SUM(sal) * 1.0 / COUNT(*),CAST(… AS DECIMAL).
3. GROUP BY — 그룹을 만드는 과정
3.1 문법 골격
SELECT 그룹키, 집계함수(…) [, …]
FROM 테이블
[WHERE 행 조건]
GROUP BY 그룹키 [, 그룹키2 …]
[HAVING 그룹 조건]
[ORDER BY …];
3.2 동작 원리 — 중간 표로 펼치기
SELECT dno, COUNT(*) AS 인원, SUM(sal) AS 합계, AVG(sal) AS 평균, MAX(sal) AS 최고
FROM 사원
GROUP BY dno;
① GROUP BY 직후(그룹을 만든 상태) — DBMS 는 dno 값이 같은 행끼리 "봉투"에 넣는다.
| 그룹(dno) | 그룹에 들어간 행 (ename, sal) |
|---|---|
| 10 | 김하늘 500 · 이도윤 350 · 박서준 280 |
| 20 | 최지우 450 · 정민재 300 · 한유나 300 |
| NULL | 오세린 250 |
② SELECT — 봉투 하나당 한 줄로 접기
| dno | 인원 | 합계 | 평균 | 최고 |
|---|---|---|---|---|
| 10 | 3 | 1130 | 376.67 | 500 |
| 20 | 3 | 1050 | 350 | 450 |
| NULL | 1 | 250 | 250 | 250 |
- 결과 행 수 = 그룹 수(= 그룹 키의 서로 다른 값 개수, NULL 포함) = 3.
- 표준 SQL 은 GROUP BY 가 정렬을 보장하지 않는다. 순서가 필요하면 ORDER BY 를 따로 쓴다 (SQLite 는 NULL 그룹이 맨 위, PostgreSQL 은 맨 아래로 나왔다 — §10).
3.3 GROUP BY 와 DISTINCT
집계 없이 그룹 키만 고르면 DISTINCT 와 결과가 같다.
SELECT dno FROM 사원 GROUP BY dno; -- 10, 20, NULL
SELECT DISTINCT dno FROM 사원; -- 10, 20, NULL
둘 다 3행. 차이는 목적이다 — DISTINCT 는 "중복 제거", GROUP BY 는 "그룹마다 집계하려고 묶기". 집계가 필요하면 GROUP BY, 단순히 종류만 보려면 DISTINCT.
3.4 식으로 그룹 만들기
그룹 키는 열이 아니라 식이어도 된다.
SELECT CASE WHEN sal >= 400 THEN '고급여' ELSE '일반' END AS 구간,
COUNT(*), AVG(sal)
FROM 사원
GROUP BY CASE WHEN sal >= 400 THEN '고급여' ELSE '일반' END;
| 구간 | COUNT(*) | AVG(sal) |
|---|---|---|
| 고급여 | 2 | 475 |
| 일반 | 5 | 296 |
(고급여 = 김하늘 500, 최지우 450 / 일반 = 350 + 280 + 300 + 300 + 250 = 1480 ÷ 5 = 296.)
표준대로라면 GROUP BY 에 같은 식을 다시 써야 한다. GROUP BY 구간 처럼 별칭을 쓰는 것은
엔진 확장이다(§8.2).
4. SELECT 에 올 수 있는 것 — 그룹 키와 집계뿐
4.1 규칙
GROUP BY 가 있으면 SELECT(그리고 HAVING, ORDER BY)에 쓸 수 있는 것은 다음뿐이다.
- 그룹 키(GROUP BY 에 적은 열·식)
- 집계 함수
- 상수, 그리고 위 둘로 만든 식 (
MAX(sal) - MIN(sal),COUNT(*) * 100,dno + 1등)
SELECT dno, MAX(sal) - MIN(sal) AS 폭, COUNT(*) * 100 AS x
FROM 사원 WHERE dno IS NOT NULL
GROUP BY dno;
| dno | 폭 | x |
|---|---|---|
| 10 | 220 | 300 |
| 20 | 150 | 300 |
4.2 왜 다른 열은 안 되는가
SELECT dno, ename, COUNT(*) FROM 사원 GROUP BY dno; -- ✗
10번 그룹 봉투에는 김하늘·이도윤·박서준 세 명이 들어 있는데 결과는 그룹당 한 줄이다.
ename 칸에 누구를 적을지 정할 방법이 없다. 그래서 표준 SQL 은 오류로 막는다.
| 엔진 | 결과 |
|---|---|
| 표준 / Oracle | 오류 (Oracle: ORA-00979: not a GROUP BY expression) |
| PostgreSQL | 오류 column "사원.ename" must appear in the GROUP BY clause or be used in an aggregate function (실행 확인) |
| MySQL 5.7+ | 기본 설정(ONLY_FULL_GROUP_BY)에서 오류. 이 모드를 끄면 그룹 안의 임의의 한 행 값을 보여 줌 |
| SQLite | 오류 없이 실행 → 임의의 한 행 값(실행 결과 10번 그룹에 '김하늘'). 신뢰하면 안 됨 |
예외: 함수 종속 — 그룹 키가 기본키이면 그 테이블의 다른 열은 그룹 안에서 값이 하나로 정해진다. 그래서 PostgreSQL·MySQL 8 은
GROUP BY d.dno만으로d.dname을 SELECT 하는 것을 허용한다(실행 확인). Oracle 은 허용하지 않는다. 시험 답안에서는 SELECT 에 쓴 비집계 열을 전부 GROUP BY 에 적는 것이 어떤 엔진에서도 통하는 정석이다.
4.3 집계 함수의 중첩
SELECT MAX(AVG(sal)) FROM 사원 GROUP BY dno;
- Oracle 만 허용(그룹별 AVG 를 구한 뒤 그중 MAX → 376.67). 이때 SELECT 에 그룹 키를 함께 쓸 수 없다.
- PostgreSQL(
aggregate function calls cannot be nested), SQLite, MySQL 은 오류. - 표준 방법은 서브쿼리로 두 단계로 나누는 것.
SELECT MAX(평균) FROM (SELECT AVG(sal) AS 평균 FROM 사원 GROUP BY dno) t; -- 376.67
5. 여러 열로 그룹 만들기
GROUP BY 에 열을 여러 개 적으면 값의 조합이 같은 행끼리 묶인다.
SELECT dno, job, COUNT(*)
FROM 사원
GROUP BY dno, job
ORDER BY dno, job;
| dno | job | COUNT(*) |
|---|---|---|
| 10 | 대리 | 1 |
| 10 | 사원 | 1 |
| 10 | 팀장 | 1 |
| 20 | 사원 | 2 |
| 20 | 팀장 | 1 |
| NULL | 사원 | 1 |
(PostgreSQL 기준 NULL 이 마지막. SQLite 는 NULL 행이 맨 위.)
- 그룹 수 = (dno, job) 조합의 서로 다른 개수 = 6. dno 종류(3) × job 종류(3) = 9 가 아니다 — 실제로 존재하는 조합만 그룹이 된다.
GROUP BY dno, job과GROUP BY job, dno는 같은 그룹을 만든다. 순서는 결과 집합에 영향이 없다 (ROLLUP 에서는 순서가 중요해진다 — §11).
6. NULL 그룹
GROUP BY 에서 NULL 은 NULL 끼리 하나의 그룹으로 모인다. WHERE 의 NULL = NULL 이 UNKNOWN 인 것과
다르게 보이지만, 표준은 그룹을 만들 때 "구별되지 않는(not distinct)" 값끼리 묶는다고 정의하고
NULL 들은 서로 구별되지 않는 것으로 친다(DISTINCT 가 NULL 을 하나로 접는 것과 같은 규칙).
SELECT mgr, COUNT(*) FROM 사원 GROUP BY mgr ORDER BY mgr;
| mgr | COUNT(*) |
|---|---|
| NULL | 3 |
| 1 | 1 |
| 2 | 1 |
| 4 | 2 |
(SQLite 출력. 관리자가 없는 김하늘·최지우·오세린 세 명이 NULL 그룹 하나로.)
NULL 그룹 + 집계 대상의 NULL 이 겹치는 경우
SELECT dno, COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus)
FROM 사원 GROUP BY dno ORDER BY dno;
| dno | COUNT(*) | COUNT(bonus) | SUM(bonus) | AVG(bonus) |
|---|---|---|---|---|
| 10 | 3 | 1 | 30 | 30 |
| 20 | 3 | 2 | 70 | 35 |
| NULL | 1 | 0 | NULL | NULL |
- NULL 그룹(오세린)은 그룹으로는 존재하지만 bonus 가 전부 NULL → 그 그룹 안에서는 "빈 집합" 집계 → COUNT 0, SUM/AVG NULL (§2.4 규칙이 그룹 단위로 적용).
- 20번 그룹의 AVG 가 35 인 이유: 50 + 20 = 70 ÷ 2(한유나 NULL 제외).
함정 — "부서별 인원"을 물었는데 미배정 사원을 빼야 한다면
WHERE dno IS NOT NULL이 필요하다. 그냥 GROUP BY dno 만 하면 NULL 그룹이 결과에 한 줄 더 나온다.
7. WHERE vs HAVING
7.1 처리 시점이 다르다
| WHERE | HAVING | |
|---|---|---|
| 처리 시점 | GROUP BY 전 | GROUP BY 후 |
| 거르는 대상 | 행 | 그룹 |
| 집계 함수 | 쓸 수 없음 | 쓸 수 있음 |
| 쓸 수 있는 열 | 테이블의 모든 열 | 그룹 키, 집계 (§4 규칙과 같음) |
SELECT dno, COUNT(*) FROM 사원 WHERE COUNT(*) >= 3 GROUP BY dno; -- ✗
PostgreSQL: aggregate functions are not allowed in WHERE, SQLite: misuse of aggregate: COUNT().
WHERE 를 처리하는 시점에는 그룹이 아직 없으므로 "그룹의 행 수"를 물을 수 없다.
7.2 같은 숫자 다른 뜻 — 순서대로 따라가기
(a) WHERE 만: "급여 300 이상인 사원을 부서별로 센다"
SELECT dno, COUNT(*) FROM 사원 WHERE sal >= 300 GROUP BY dno;
| WHERE 통과 행 | → 그룹 |
|---|---|
| 김하늘 500, 이도윤 350 (박서준 280 탈락) | 10 → 2 |
| 최지우 450, 정민재 300, 한유나 300 | 20 → 3 |
| (오세린 250 탈락) | NULL 그룹 자체가 생기지 않음 |
| dno | COUNT(*) |
|---|---|
| 10 | 2 |
| 20 | 3 |
(b) HAVING 만: "인원이 3명 이상인 부서"
SELECT dno, COUNT(*) FROM 사원 GROUP BY dno HAVING COUNT(*) >= 3;
| 그룹 | COUNT | HAVING 통과? |
|---|---|---|
| 10 | 3 | ✓ |
| 20 | 3 | ✓ |
| NULL | 1 | ✗ |
| dno | COUNT(*) |
|---|---|
| 10 | 3 |
| 20 | 3 |
(c) 둘 다: "급여 300 이상인 사원이 2명 이상인 부서" — 먼저 행을 거르고, 남은 것으로 그룹을 만들고, 그룹을 다시 거른다.
SELECT dno, COUNT(*) FROM 사원
WHERE sal >= 300
GROUP BY dno
HAVING COUNT(*) >= 2;
| dno | COUNT(*) |
|---|---|
| 10 | 2 |
| 20 | 3 |
(d) 평균 조건:
SELECT dno, AVG(sal) FROM 사원 GROUP BY dno HAVING AVG(sal) > 300;
| dno | AVG(sal) |
|---|---|
| 10 | 376.67 |
| 20 | 350 |
(NULL 그룹은 250 이라 탈락.)
7.3 그룹 키 조건은 어디에 둬도 결과가 같다
SELECT dno, COUNT(*) FROM 사원 WHERE dno = 10 GROUP BY dno; -- 10 | 3
SELECT dno, COUNT(*) FROM 사원 GROUP BY dno HAVING dno = 10; -- 10 | 3
결과는 같지만 WHERE 쪽이 정석이다. 그룹을 만들기 전에 행을 줄이므로 할 일이 적다. 원칙: 집계가 들어간 조건은 HAVING, 그 외의 행 조건은 WHERE.
HAVING 에는 SELECT 에 없는 집계도 쓸 수 있다.
SELECT job, COUNT(*) FROM 사원 GROUP BY job HAVING SUM(sal) >= 600;
| job | COUNT(*) |
|---|---|
| 사원 | 4 |
| 팀장 | 2 |
(사원 280+300+300+250 = 1130, 팀장 950 통과 / 대리 350 탈락.)
8. 별칭은 어디서 쓸 수 있나
8.1 HAVING 에 별칭
SELECT dno, COUNT(*) AS cnt FROM 사원 GROUP BY dno HAVING cnt >= 3;
| 엔진 | 결과 |
|---|---|
| 표준 SQL | 불가 (HAVING 이 SELECT 보다 먼저 처리되어 cnt 가 아직 없다) |
| Oracle | 불가 (ORA-00904: invalid identifier) |
| PostgreSQL | 불가 — column "cnt" does not exist (실행 확인) |
| MySQL | 허용 |
| SQLite | 허용 (실행 확인: 10 | 3, 20 | 3) |
시험 답안은 표준대로 HAVING COUNT(*) >= 3 으로 다시 쓴다.
8.2 절별 별칭 사용 가능 여부 종합
| 절 | 표준 | Oracle | MySQL | PostgreSQL | SQLite |
|---|---|---|---|---|---|
| WHERE | ✗ | ✗ | ✗ | ✗ (실행 확인) | ○ (비표준 확장, 실행 확인) |
| GROUP BY | ✗ | ✗ (23ai 이전) | ○ | ○ (실행 확인) | ○ |
| HAVING | ✗ | ✗ | ○ | ✗ | ○ |
| ORDER BY | ○ | ○ | ○ | ○ | ○ |
표준에서 별칭을 확실히 쓸 수 있는 곳은 ORDER BY 뿐이다. 처리 순서표(§1)에서 SELECT 보다 뒤에 오는 절이 ORDER BY 하나이기 때문이다.
9. GROUP BY 없이 HAVING
HAVING 은 GROUP BY 없이도 쓸 수 있다. 이때 테이블 전체(WHERE 통과분)가 그룹 하나가 된다.
SELECT COUNT(*) FROM 사원 HAVING COUNT(*) > 5; -- 7 (1행)
SELECT COUNT(*) FROM 사원 HAVING COUNT(*) > 10; -- 결과 0행
- 조건이 참이면 1행, 거짓이면 0행. "전체 집계 결과를 조건부로 내보낸다"는 뜻이 된다.
- 이 형태에서 SELECT 에는 집계만 쓸 수 있다(그룹 키가 없으므로 일반 열은 §4 규칙 위반).
- 옛 SQLite(3.39 이전)는 GROUP BY 없는 HAVING 을 오류로 처리했다. 표준과 Oracle·PostgreSQL·MySQL 은 허용.
10. ORDER BY 와 집계
ORDER BY 는 마지막에 처리되므로 집계 함수, 별칭, 열 번호를 모두 쓸 수 있다.
SELECT dno, SUM(sal) FROM 사원 GROUP BY dno ORDER BY SUM(sal) DESC;
| dno | SUM(sal) |
|---|---|
| 10 | 1130 |
| 20 | 1050 |
| NULL | 250 |
SELECT job, COUNT(*) FROM 사원 GROUP BY job ORDER BY COUNT(*) DESC, job;
| job | COUNT(*) |
|---|---|
| 사원 | 4 |
| 팀장 | 2 |
| 대리 | 1 |
- GROUP BY 가 있을 때 ORDER BY 에도 §4 규칙이 적용된다.
ORDER BY ename은 오류(그룹 안에 ename 이 여럿). - NULL 의 정렬 위치는 엔진마다 다르다. 그룹 키에 NULL 이 있으면 결과 순서가 달라진다.
| 엔진 | ASC 에서 NULL | 바꾸는 방법 |
|---|---|---|
| Oracle | 마지막 | NULLS FIRST / NULLS LAST |
| PostgreSQL | 마지막 (실행 확인) | NULLS FIRST / NULLS LAST |
| MySQL | 처음 | ORDER BY col IS NULL, col 같은 우회 |
| SQLite | 처음 (실행 확인) | NULLS FIRST / LAST (3.30+) |
11. ROLLUP · CUBE · GROUPING SETS — 소계와 총계
여러 수준의 집계(부서·직급별 + 부서별 소계 + 전체 총계)를 한 번에 뽑는 GROUP BY 확장이다.
아래 예는 미배정(dno NULL) 사원을 빼고(WHERE dno IS NOT NULL) 6명으로 계산한다.
SQLite 는 지원하지 않아 PostgreSQL 로 실행했다.
11.1 GROUPING SETS — 원하는 묶음을 직접 나열
GROUP BY GROUPING SETS ( (그룹1), (그룹2), …, () )
() 는 "아무것으로도 묶지 않음" = 전체 총계. GROUPING SETS 는 각 묶음으로 GROUP BY 한 결과를
UNION ALL 로 이어 붙인 것과 같다. 그 묶음에 없는 열은 NULL 로 채워진다.
SELECT dno, job, SUM(sal)
FROM 사원 WHERE dno IS NOT NULL
GROUP BY GROUPING SETS ((dno), (job));
| dno | job | SUM(sal) | 어느 묶음? |
|---|---|---|---|
| 10 | NULL | 1130 | (dno) |
| 20 | NULL | 1050 | (dno) |
| NULL | 대리 | 350 | (job) |
| NULL | 사원 | 880 | (job) |
| NULL | 팀장 | 950 | (job) |
행 수 = dno 종류 2 + job 종류 3 = 5. (사원 = 280 + 300 + 300 = 880, 팀장 = 500 + 450 = 950.)
11.2 ROLLUP — 오른쪽부터 하나씩 떼며 소계
GROUP BY ROLLUP(a, b) ≡ GROUPING SETS ((a, b), (a), ())
GROUP BY ROLLUP(a, b, c) ≡ GROUPING SETS ((a, b, c), (a, b), (a), ())
열이 n 개면 묶음은 n + 1 개. 계층(부서 → 직급) 소계용이라 열 순서가 결과를 바꾼다.
SELECT dno, job, SUM(sal)
FROM 사원 WHERE dno IS NOT NULL
GROUP BY ROLLUP(dno, job)
ORDER BY dno, job;
| dno | job | SUM(sal) | 행 종류 |
|---|---|---|---|
| 10 | 대리 | 350 | (dno, job) |
| 10 | 사원 | 280 | (dno, job) |
| 10 | 팀장 | 500 | (dno, job) |
| 10 | NULL | 1130 | 10번 소계 |
| 20 | 사원 | 600 | (dno, job) |
| 20 | 팀장 | 450 | (dno, job) |
| 20 | NULL | 1050 | 20번 소계 |
| NULL | NULL | 2180 | 총계 |
결과 행 수 계산법: 각 묶음의 그룹 수를 더한다.
| 묶음 | 그룹 수 |
|---|---|
| (dno, job) | 실제 존재하는 조합 5 |
| (dno) | 2 |
| () | 1 |
| 합계 | 8 |
ROLLUP(job, dno) 로 순서를 바꾸면 묶음이 (job, dno), (job), () 가 되어 5 + 3 + 1 = 9행이 되고,
소계도 "직급별 소계"로 바뀐다.
11.3 CUBE — 가능한 모든 조합
GROUP BY CUBE(a, b) ≡ GROUPING SETS ((a, b), (a), (b), ())
열이 n 개면 묶음은 2ⁿ 개(각 열을 넣거나 빼거나). 순서는 결과 집합에 영향이 없다.
SELECT dno, job, SUM(sal)
FROM 사원 WHERE dno IS NOT NULL
GROUP BY CUBE(dno, job)
ORDER BY dno, job;
| dno | job | SUM(sal) | 행 종류 |
|---|---|---|---|
| 10 | 대리 | 350 | (dno, job) |
| 10 | 사원 | 280 | (dno, job) |
| 10 | 팀장 | 500 | (dno, job) |
| 10 | NULL | 1130 | 부서 소계 |
| 20 | 사원 | 600 | (dno, job) |
| 20 | 팀장 | 450 | (dno, job) |
| 20 | NULL | 1050 | 부서 소계 |
| NULL | 대리 | 350 | 직급 소계 |
| NULL | 사원 | 880 | 직급 소계 |
| NULL | 팀장 | 950 | 직급 소계 |
| NULL | NULL | 2180 | 총계 |
행 수 = 5 + 2 + 3 + 1 = 11. ROLLUP 결과(8)에 직급 소계 3행이 더해진 모양.
| 묶음 수(열 n개) | 예: (dno, job) 행 수 | 열 순서 영향 | |
|---|---|---|---|
| GROUP BY a, b | 1 | 5 | 없음 |
| ROLLUP(a, b) | n + 1 = 3 | 5 + 2 + 1 = 8 | 있음 |
| CUBE(a, b) | 2ⁿ = 4 | 5 + 2 + 3 + 1 = 11 | 없음 |
| GROUPING SETS((a),(b)) | 적은 만큼 = 2 | 2 + 3 = 5 | 없음 |
11.4 GROUPING 함수 — 진짜 NULL 과 소계 NULL 구별
소계 행의 NULL 은 "이 열로 묶지 않았다"는 표시일 뿐 데이터의 NULL 이 아니다. 그런데 그룹 키 자체에 NULL 이 있으면 둘이 섞인다. 미배정 사원을 빼지 않고 ROLLUP 해 보자.
SELECT dno, COUNT(*) FROM 사원 GROUP BY ROLLUP(dno) ORDER BY dno;
| dno | COUNT(*) |
|---|---|
| 10 | 3 |
| 20 | 3 |
| NULL | 7 |
| NULL | 1 |
dno 가 NULL 인 행이 두 개다 — 하나는 미배정 그룹(1명), 하나는 총계(7명). 눈으로는 구별이 안 된다.
GROUPING(열) 은 그 열이 소계 때문에 NULL 이 된 행이면 1, 아니면 0 을 돌려준다.
SELECT dno, job, SUM(sal), GROUPING(dno) AS gd, GROUPING(job) AS gj
FROM 사원
GROUP BY ROLLUP(dno, job)
ORDER BY dno, job;
| dno | job | SUM(sal) | gd | gj | 의미 |
|---|---|---|---|---|---|
| 10 | 대리 | 350 | 0 | 0 | 상세 |
| 10 | 사원 | 280 | 0 | 0 | 상세 |
| 10 | 팀장 | 500 | 0 | 0 | 상세 |
| 10 | NULL | 1130 | 0 | 1 | 10번 소계 |
| 20 | 사원 | 600 | 0 | 0 | 상세 |
| 20 | 팀장 | 450 | 0 | 0 | 상세 |
| 20 | NULL | 1050 | 0 | 1 | 20번 소계 |
| NULL | 사원 | 250 | 0 | 0 | 미배정 그룹의 상세(진짜 NULL) |
| NULL | NULL | 250 | 0 | 1 | 미배정 그룹 소계 |
| NULL | NULL | 2430 | 1 | 1 | 총계 |
gd 를 보면 끝의 두 행(250 소계와 2430 총계)이 구별된다. 보고서용으로 라벨을 붙이는 정석:
SELECT CASE WHEN GROUPING(dno) = 1 THEN '전체' ELSE CAST(dno AS VARCHAR) END AS 부서,
CASE WHEN GROUPING(job) = 1 THEN '소계' ELSE job END AS 직급,
SUM(sal)
FROM 사원 WHERE dno IS NOT NULL
GROUP BY ROLLUP(dno, job)
ORDER BY GROUPING(dno), dno, GROUPING(job), job;
| 부서 | 직급 | SUM(sal) |
|---|---|---|
| 10 | 대리 | 350 |
| 10 | 사원 | 280 |
| 10 | 팀장 | 500 |
| 10 | 소계 | 1130 |
| 20 | 사원 | 600 |
| 20 | 팀장 | 450 |
| 20 | 소계 | 1050 |
| 전체 | 소계 | 2180 |
11.5 엔진별 지원
| 엔진 | ROLLUP | CUBE | GROUPING SETS | GROUPING() |
|---|---|---|---|---|
| 표준 SQL (SQL:1999) | ○ | ○ | ○ | ○ |
| Oracle | ○ | ○ | ○ | ○ (+ GROUPING_ID) |
| PostgreSQL 9.5+ | ○ | ○ | ○ | ○ |
| MySQL | GROUP BY a, b WITH ROLLUP 형식 | ✗ | ✗ | 8.0+ |
| SQLite | ✗ | ✗ | ✗ | ✗ |
12. 조건부 집계 — SUM(CASE …)
집계 함수 안에 CASE 를 넣으면 그룹 안에서 조건에 맞는 행만 골라 집계할 수 있다. WHERE 로 거르면 다른 집계까지 같이 줄어들지만, 조건부 집계는 한 SELECT 안에서 조건별 집계를 나란히 낸다.
SELECT dno,
SUM(CASE WHEN job = '사원' THEN 1 ELSE 0 END) AS 사원수,
SUM(CASE WHEN job = '팀장' THEN 1 ELSE 0 END) AS 팀장수,
COUNT(CASE WHEN bonus IS NOT NULL THEN 1 END) AS 보너스받는수
FROM 사원
GROUP BY dno
ORDER BY dno;
| dno | 사원수 | 팀장수 | 보너스받는수 |
|---|---|---|---|
| 10 | 1 | 1 | 1 |
| 20 | 2 | 1 | 2 |
| NULL | 1 | 0 | 0 |
SUM(CASE … THEN 1 ELSE 0 END)은 조건을 만족하는 행 수.COUNT(CASE … THEN 1 END)도 같은 수다. ELSE 가 없으면 나머지는 NULL 이 되고, COUNT 는 NULL 을 세지 않기 때문이다. 반대로COUNT(CASE … THEN 1 ELSE 0 END)라고 쓰면 0 도 값이므로 모든 행을 센다 — 흔한 실수.- PostgreSQL 은
COUNT(*) FILTER (WHERE job = '사원')이라는 표준 문법(SQL:2003)도 지원한다. 결과 동일.
| 식 | 10번 그룹(팀장·대리·사원) 결과 |
|---|---|
SUM(CASE WHEN job='사원' THEN 1 ELSE 0 END) | 1 |
COUNT(CASE WHEN job='사원' THEN 1 END) | 1 |
COUNT(CASE WHEN job='사원' THEN 1 ELSE 0 END) | 3 (함정) |
SUM(CASE WHEN job='사원' THEN sal END) | 280 (조건 행의 급여 합) |
13. 피벗 — 행을 열로 돌리기
조건부 집계를 값마다 열 하나씩 만들면 행 방향 데이터가 열 방향 표(피벗, 교차표)가 된다.
원래 모양(세로) — GROUP BY job, dno 결과
| job | dno | SUM(sal) |
|---|---|---|
| 대리 | 10 | 350 |
| 사원 | 10 | 280 |
| 사원 | 20 | 600 |
| 사원 | NULL | 250 |
| 팀장 | 10 | 500 |
| 팀장 | 20 | 450 |
피벗(가로) — 직급을 행, 부서를 열로
SELECT job,
SUM(CASE WHEN dno = 10 THEN sal ELSE 0 END) AS d10,
SUM(CASE WHEN dno = 20 THEN sal ELSE 0 END) AS d20,
SUM(CASE WHEN dno IS NULL THEN sal ELSE 0 END) AS 미배정
FROM 사원
GROUP BY job
ORDER BY job;
| job | d10 | d20 | 미배정 |
|---|---|---|---|
| 대리 | 350 | 0 | 0 |
| 사원 | 280 | 600 | 250 |
| 팀장 | 500 | 450 | 0 |
- 미배정 열을
dno = NULL로 쓰면 항상 UNKNOWN 이라 전부 0 이 된다.IS NULL로 써야 한다. ELSE 0을 빼면 해당 값이 없는 칸은 0 이 아니라 NULL 이 된다(빈 집합 SUM).- 열로 만들 값(10, 20, NULL)을 쿼리에 미리 적어야 한다. 새 부서가 생기면 쿼리를 고쳐야 한다.
Oracle 의 PIVOT 절 (11g+, 실행은 못 하고 문법만):
SELECT *
FROM (SELECT job, dno, sal FROM 사원)
PIVOT (SUM(sal) FOR dno IN (10 AS d10, 20 AS d20));
조건부 집계와 같은 결과지만 값이 없는 칸은 0 이 아니라 NULL 이다. 반대 방향(열 → 행)은 UNPIVOT.
PostgreSQL 은 tablefunc 확장의 crosstab(), MySQL·SQLite 는 PIVOT 이 없어 조건부 집계로 쓴다.
14. 함정 모음
| 함정 | 틀린 생각 | 실제 |
|---|---|---|
COUNT(col) | 행 수 | NULL 이 아닌 값의 수 |
COUNT(DISTINCT col) | NULL 도 한 종류 | NULL 은 세지 않음 |
AVG(col) | 합 ÷ 전체 행 수 | 합 ÷ NULL 아닌 행 수 |
빈 집합 SUM | 0 | NULL |
| 빈 집합 + GROUP BY | COUNT 0 인 행 1개 | 0행 |
| NULL 그룹 | NULL 행은 그룹에서 빠짐 | NULL 끼리 한 그룹 |
| WHERE 에 집계 | 가능 | 오류 — HAVING 으로 |
| HAVING 에 별칭 | 가능 | 표준·Oracle·PostgreSQL 오류 (MySQL·SQLite 만 허용) |
| SELECT 에 비집계 열 | 아무 값이나 나옴 | 표준은 오류 |
| ROLLUP 열 순서 | 상관없음 | 소계 계층이 바뀜 |
COUNT(CASE … ELSE 0 END) | 조건 행 수 | 전체 행 수 |
확인 문제
문제 1 (결과 쓰기) 다음 쿼리의 결과를 쓰시오.
SELECT COUNT(*), COUNT(bonus), COUNT(DISTINCT job) FROM 사원;
문제 2 (OX) SELECT AVG(bonus) FROM 사원; 과 SELECT AVG(COALESCE(bonus, 0)) FROM 사원; 의 결과는
같다. ( )
문제 3 (결과 쓰기) 결과의 행 수와 각 행을 쓰시오.
SELECT dno, COUNT(*) FROM 사원
WHERE job = '사원'
GROUP BY dno
HAVING COUNT(*) >= 1;
문제 4 (빈칸) 평균 급여가 350 이상인 부서의 부서번호와 평균 급여를 구한다. 빈칸을 채우시오.
SELECT dno, AVG(sal) FROM 사원
GROUP BY dno
( ① ) AVG(sal) >= 350;
문제 5 (결과 쓰기) 두 쿼리의 결과를 각각 쓰시오.
SELECT COUNT(*), SUM(sal) FROM 사원 WHERE sal > 1000; -- (가)
SELECT dno, COUNT(*), SUM(sal) FROM 사원 WHERE sal > 1000 GROUP BY dno; -- (나)
문제 6 (계산) WHERE dno IS NOT NULL 로 6명만 남긴 뒤 GROUP BY ROLLUP(dno, job) 과
GROUP BY CUBE(dno, job) 을 했을 때 결과 행 수를 각각 쓰시오. (dno 2종, job 3종, 실제 (dno, job) 조합 5종)
문제 7 (OX) SELECT dno, ename, COUNT(*) FROM 사원 GROUP BY dno; 는 표준 SQL 에서 오류다. ( )
문제 8 (결과 쓰기)
SELECT dno, SUM(CASE WHEN sal >= 300 THEN 1 ELSE 0 END) AS 고급여
FROM 사원
GROUP BY dno;
정답과 풀이
- 7, 3, 3 — 행 7개 / bonus 값은 30·50·20 셋 / job 종류는 팀장·대리·사원 셋.
- X — 앞은 100 ÷ 3 = 33.33, 뒤는 100 ÷ 7 = 14.29. AVG 는 NULL 을 분모에서 뺀다.
- 3행 — (10, 1), (20, 2), (NULL, 1). WHERE 로 '사원' 4명(박서준, 정민재, 한유나, 오세린)만 남기고 묶으면 NULL 그룹(오세린)도 하나의 그룹이 되고 COUNT 1 ≥ 1 이라 통과한다.
- ① HAVING — 집계 조건이라 WHERE 불가. 결과: (10, 376.67), (20, 350). 350 은
>=이라 포함. - (가) 1행: 0, NULL / (나) 0행 — GROUP BY 가 없으면 빈 집합도 1행으로 집계하고 SUM 은 NULL. GROUP BY 가 있으면 만들어진 그룹이 없으므로 결과가 없다.
- ROLLUP 8행, CUBE 11행 — ROLLUP = (dno,job) 5 + (dno) 2 + () 1. CUBE = 5 + 2 + (job) 3 + 1.
- O — ename 이 그룹 키도 집계도 아니다. 10번 그룹엔 이름이 셋이라 한 칸에 무엇을 쓸지 정할 수 없다. (MySQL 의 ONLY_FULL_GROUP_BY 해제 상태, SQLite 는 임의 값으로 실행되지만 표준은 오류.)
- (10, 2), (20, 3), (NULL, 0) — 10번은 500·350 통과(280 제외), 20번은 450·300·300, 미배정 오세린 250 은 조건 불만족이지만 그룹은 존재하므로 0 으로 나온다(WHERE 로 걸렀다면 그 그룹이 사라졌을 것).
한 줄 요약
| 주제 | 한 줄 |
|---|---|
| 처리 순서 | FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY |
| 집계와 NULL | COUNT(*) 만 NULL 포함, 나머지 집계는 NULL 행을 없는 것으로 본다 |
| AVG | 합 ÷ NULL 아닌 개수. NULL 을 0 으로 보려면 COALESCE |
| 빈 집합 | GROUP BY 없음 → 1행(COUNT 0, SUM NULL) / GROUP BY 있음 → 0행 |
| 정수 AVG | 대부분 소수, SQL Server 만 버림. 7/2 는 PostgreSQL·SQLite·SQL Server 에서 3 |
| GROUP BY | 그룹 1개 = 결과 1행. 여러 열이면 실제 존재하는 조합 수만큼 |
| SELECT 규칙 | 그룹 키, 집계, 그 둘로 만든 식만. 위반 시 표준 오류(MySQL·SQLite 는 느슨) |
| NULL 그룹 | NULL 끼리 한 그룹으로 결과에 한 줄 |
| WHERE vs HAVING | 행 필터는 WHERE(그룹 전), 집계 조건은 HAVING(그룹 후) |
| 별칭 | 표준에서 확실한 곳은 ORDER BY 뿐 |
| HAVING 단독 | 전체가 그룹 하나 → 참이면 1행, 거짓이면 0행 |
| ROLLUP / CUBE | 묶음 수 n+1 / 2ⁿ, 행 수 = 각 묶음 그룹 수의 합 |
| GROUPING() | 소계 때문에 NULL 이면 1 — 진짜 NULL 과 구별 |
| 조건부 집계 | SUM(CASE … THEN 1 ELSE 0 END) = COUNT(CASE … THEN 1 END) |
| 피벗 | 열마다 조건부 집계 하나. Oracle 은 PIVOT 절 |