← 정보처리기사 실기 개념정리 목차

SQL 완전 정리 ③ 집계·GROUP BY·HAVING

앞의 두 편은 행 하나하나를 걸러 내고(WHERE) 골라 보여 주는(SELECT) 법이었다. 이 편부터는 여러 행을 하나의 값으로 접는 법을 다룬다. 집계 함수는 "행 여러 개 → 값 하나", GROUP BY 는 "그 접기를 그룹마다 따로", HAVING 은 "접은 결과를 다시 거르기"다. 세 가지가 각각 논리적 처리 순서의 어디에 끼어드는지만 잡으면 이 단원의 함정(별칭, WHERE 의 집계, NULL 그룹, 빈 집합)은 전부 같은 원리로 풀린다. 본문의 모든 쿼리와 결과 표는 SQLite 와 PostgreSQL 에서 실제로 실행해 맞췄고, ROLLUP·CUBE·GROUPING SETS 는 PostgreSQL 로 검증했다.


0. 이 편에서 쓰는 테이블

사원

enoenamednojobsalmgrhiredatebonus
1김하늘10팀장500NULL2019-03-01NULL
2이도윤10대리35012021-07-1530
3박서준10사원28022023-01-02NULL
4최지우20팀장450NULL2018-11-2050
5정민재20사원30042022-05-0920
6한유나20사원30042024-02-01NULL
7오세린NULL사원250NULL2025-01-06NULL

참여

enopnohours
110020
210040
310030
220010
520025

눈여겨볼 곳: 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)
736210033.33…5020

한 칸씩 풀어 보면:

결과계산 과정
COUNT(*) = 7행이 7개
COUNT(bonus) = 330, 50, 20 만 값. NULL 4개 제외
COUNT(DISTINCT sal) = 6500, 350, 280, 450, 300, 300, 250 → 300 중복 접기 → 6
COUNT(DISTINCT dno) = 210, 20 (NULL 은 "서로 다른 값"으로도 세지 않는다)
SUM(bonus) = 10030 + 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)
2430347.14…한유나2018-11-202025-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)
00NULLNULLNULLNULL
  • 결과는 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;
dnoCOUNT(*)
(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
OracleNUMBER376.666666…3.5
MySQLDECIMAL376.66673.5000
PostgreSQLnumeric376.66666666666666673
SQLiteREAL(실수)376.6666666666673
SQL ServerINT376 (소수 버림)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인원합계평균최고
1031130376.67500
2031050350450
NULL1250250250
  • 결과 행 수 = 그룹 수(= 그룹 키의 서로 다른 값 개수, 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)
고급여2475
일반5296

(고급여 = 김하늘 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)에 쓸 수 있는 것은 다음뿐이다.

  1. 그룹 키(GROUP BY 에 적은 열·식)
  2. 집계 함수
  3. 상수, 그리고 위 둘로 만든 식 (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
10220300
20150300

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;
dnojobCOUNT(*)
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;
mgrCOUNT(*)
NULL3
11
21
42

(SQLite 출력. 관리자가 없는 김하늘·최지우·오세린 세 명이 NULL 그룹 하나로.)

NULL 그룹 + 집계 대상의 NULL 이 겹치는 경우

SELECT dno, COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus)
FROM 사원 GROUP BY dno ORDER BY dno;
dnoCOUNT(*)COUNT(bonus)SUM(bonus)AVG(bonus)
10313030
20327035
NULL10NULLNULL
  • 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 처리 시점이 다르다

WHEREHAVING
처리 시점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, 한유나 30020 → 3
(오세린 250 탈락)NULL 그룹 자체가 생기지 않음
dnoCOUNT(*)
102
203

(b) HAVING 만: "인원이 3명 이상인 부서"

SELECT dno, COUNT(*) FROM 사원 GROUP BY dno HAVING COUNT(*) >= 3;
그룹COUNTHAVING 통과?
103✓
203✓
NULL1✗
dnoCOUNT(*)
103
203

(c) 둘 다: "급여 300 이상인 사원이 2명 이상인 부서" — 먼저 행을 거르고, 남은 것으로 그룹을 만들고, 그룹을 다시 거른다.

SELECT dno, COUNT(*) FROM 사원
WHERE sal >= 300
GROUP BY dno
HAVING COUNT(*) >= 2;
dnoCOUNT(*)
102
203

(d) 평균 조건:

SELECT dno, AVG(sal) FROM 사원 GROUP BY dno HAVING AVG(sal) > 300;
dnoAVG(sal)
10376.67
20350

(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;
jobCOUNT(*)
사원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 절별 별칭 사용 가능 여부 종합

절표준OracleMySQLPostgreSQLSQLite
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;
dnoSUM(sal)
101130
201050
NULL250
SELECT job, COUNT(*) FROM 사원 GROUP BY job ORDER BY COUNT(*) DESC, job;
jobCOUNT(*)
사원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));
dnojobSUM(sal)어느 묶음?
10NULL1130(dno)
20NULL1050(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;
dnojobSUM(sal)행 종류
10대리350(dno, job)
10사원280(dno, job)
10팀장500(dno, job)
10NULL113010번 소계
20사원600(dno, job)
20팀장450(dno, job)
20NULL105020번 소계
NULLNULL2180총계

결과 행 수 계산법: 각 묶음의 그룹 수를 더한다.

묶음그룹 수
(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;
dnojobSUM(sal)행 종류
10대리350(dno, job)
10사원280(dno, job)
10팀장500(dno, job)
10NULL1130부서 소계
20사원600(dno, job)
20팀장450(dno, job)
20NULL1050부서 소계
NULL대리350직급 소계
NULL사원880직급 소계
NULL팀장950직급 소계
NULLNULL2180총계

행 수 = 5 + 2 + 3 + 1 = 11. ROLLUP 결과(8)에 직급 소계 3행이 더해진 모양.

묶음 수(열 n개)예: (dno, job) 행 수열 순서 영향
GROUP BY a, b15없음
ROLLUP(a, b)n + 1 = 35 + 2 + 1 = 8있음
CUBE(a, b)2ⁿ = 45 + 2 + 3 + 1 = 11없음
GROUPING SETS((a),(b))적은 만큼 = 22 + 3 = 5없음

11.4 GROUPING 함수 — 진짜 NULL 과 소계 NULL 구별

소계 행의 NULL 은 "이 열로 묶지 않았다"는 표시일 뿐 데이터의 NULL 이 아니다. 그런데 그룹 키 자체에 NULL 이 있으면 둘이 섞인다. 미배정 사원을 빼지 않고 ROLLUP 해 보자.

SELECT dno, COUNT(*) FROM 사원 GROUP BY ROLLUP(dno) ORDER BY dno;
dnoCOUNT(*)
103
203
NULL7
NULL1

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;
dnojobSUM(sal)gdgj의미
10대리35000상세
10사원28000상세
10팀장50000상세
10NULL11300110번 소계
20사원60000상세
20팀장45000상세
20NULL10500120번 소계
NULL사원25000미배정 그룹의 상세(진짜 NULL)
NULLNULL25001미배정 그룹 소계
NULLNULL243011총계

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 엔진별 지원

엔진ROLLUPCUBEGROUPING SETSGROUPING()
표준 SQL (SQL:1999)○○○○
Oracle○○○○ (+ GROUPING_ID)
PostgreSQL 9.5+○○○○
MySQLGROUP 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사원수팀장수보너스받는수
10111
20212
NULL100
  • 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 결과

jobdnoSUM(sal)
대리10350
사원10280
사원20600
사원NULL250
팀장10500
팀장20450

피벗(가로) — 직급을 행, 부서를 열로

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;
jobd10d20미배정
대리35000
사원280600250
팀장5004500
  • 미배정 열을 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 아닌 행 수
빈 집합 SUM0NULL
빈 집합 + GROUP BYCOUNT 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;

정답과 풀이

  1. 7, 3, 3 — 행 7개 / bonus 값은 30·50·20 셋 / job 종류는 팀장·대리·사원 셋.
  2. X — 앞은 100 ÷ 3 = 33.33, 뒤는 100 ÷ 7 = 14.29. AVG 는 NULL 을 분모에서 뺀다.
  3. 3행 — (10, 1), (20, 2), (NULL, 1). WHERE 로 '사원' 4명(박서준, 정민재, 한유나, 오세린)만 남기고 묶으면 NULL 그룹(오세린)도 하나의 그룹이 되고 COUNT 1 ≥ 1 이라 통과한다.
  4. ① HAVING — 집계 조건이라 WHERE 불가. 결과: (10, 376.67), (20, 350). 350 은 >= 이라 포함.
  5. (가) 1행: 0, NULL / (나) 0행 — GROUP BY 가 없으면 빈 집합도 1행으로 집계하고 SUM 은 NULL. GROUP BY 가 있으면 만들어진 그룹이 없으므로 결과가 없다.
  6. ROLLUP 8행, CUBE 11행 — ROLLUP = (dno,job) 5 + (dno) 2 + () 1. CUBE = 5 + 2 + (job) 3 + 1.
  7. O — ename 이 그룹 키도 집계도 아니다. 10번 그룹엔 이름이 셋이라 한 칸에 무엇을 쓸지 정할 수 없다. (MySQL 의 ONLY_FULL_GROUP_BY 해제 상태, SQLite 는 임의 값으로 실행되지만 표준은 오류.)
  8. (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
집계와 NULLCOUNT(*) 만 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 절
← SQL ② 함수·NULL·CASESQL ④ JOIN →