MySQL SUM(IF()) — 피벗 테이블 만들기 완전 정리

 

 

Database · MySQL

MySQL SUM(IF()) — 피벗 테이블 만들기 완전 정리

행으로 쌓인 계절별 데이터를 사람별 열 구조로 바꾸는 조건부 집계 패턴

시작 오늘 분석할 코드

원본 예제는 pivotTest 테이블에 이름, 계절, 수량을 행 단위로 저장한 뒤 SUM(IF())로 계절별 합계를 열처럼 펼친다. 리포트 화면에서 가장 자주 쓰는 "행 데이터를 가로 표로 바꾸기" 패턴이다.

CREATE TABLE pivotTest ( uName CHAR(3), season CHAR(2), amount INT ); SELECT uName, SUM(IF(season='봄', amount, 0)) AS '봄', SUM(IF(season='여름', amount, 0)) AS '여름', SUM(IF(season='가을', amount, 0)) AS '가을', SUM(IF(season='겨울', amount, 0)) AS '겨울', SUM(amount) AS '합계' FROM pivotTest GROUP BY uName;

실제 결과 미리보기

uName	봄	여름	가을	겨울	합계
김범수	40	14	25	32	111
성시경	0	79	0	40	119

용어 용어 정리

SUM()여러 행의 숫자를 더하는 집계 함수

IF()조건이 참이면 두 번째 값, 거짓이면 세 번째 값을 반환하는 함수

SUM(IF())조건에 맞는 행만 값으로 남기고 나머지는 0으로 만들어 합산하는 조건부 집계

GROUP BY같은 이름이나 같은 분류끼리 묶어 집계하는 구문

pivot행으로 쌓인 값을 열 방향으로 펼쳐 리포트 표 형태로 바꾸는 방식

amount집계 대상이 되는 수량 컬럼

aliasAS '봄'처럼 결과 컬럼에 붙이는 출력 이름

조건부 집계하나의 집계 함수 안에서 조건을 걸어 필요한 값만 합산하는 기법

1 왜 SUM 안에 IF를 넣는가

조건을 만족하는 행만 합계에 참여시킨다

IF(season='봄', amount, 0)은 계절이 봄인 행에서는 수량을 반환하고, 봄이 아닌 행에서는 0을 반환한다. 여기에 SUM()을 씌우면 봄 행의 수량만 더해진다.

봄 행: IF(TRUE, amount, 0) → amount 여름 행: IF(FALSE, amount, 0) → 0 가을 행: IF(FALSE, amount, 0) → 0 겨울 행: IF(FALSE, amount, 0) → 0 SUM(...) → 봄 수량만 합산

핵심은 조건에 맞지 않는 행을 NULL이 아니라 0으로 만드는 것이다. 그래야 합계가 숫자로 안정적으로 나온다.

2 GROUP BY가 행을 묶고 SUM(IF)가 열을 만든다

GROUP BY uName은 사람별로 행을 묶는다. 그 안에서 SUM(IF(season='봄', ...)), SUM(IF(season='여름', ...))을 각각 계산하면 사람별 결과 행 하나에 계절별 열이 생긴다.

1

먼저 이름별로 묶기김범수 행은 김범수끼리, 성시경 행은 성시경끼리 한 그룹이 된다.

2

각 그룹 안에서 계절 조건 검사봄 열은 봄인 행만, 여름 열은 여름인 행만 값으로 남긴다.

3

열마다 별도 SUM 실행각 계절 조건식이 독립된 결과 컬럼으로 계산된다.

4

마지막 합계 계산SUM(amount)는 조건 없이 전체 수량을 더해 행 합계를 만든다.

3 원본 행 데이터와 피벗 결과 비교

원본 테이블

한 행에 이름, 계절, 수량이 하나씩 들어간다. 데이터 입력과 저장에는 편하지만, 보고서처럼 비교하기에는 세로로 길다.

피벗 결과

이름 하나당 한 행이 되고 계절이 열로 펼쳐진다. 화면 출력, 엑셀 복사, 계절별 비교에 적합하다.

구분	장점	주의점
원본 행 구조	입력·추가·검색이 단순하다	리포트에서는 같은 이름이 여러 줄로 보인다
피벗 결과 구조	한눈에 계절별 비교가 된다	새 계절이 생기면 SELECT 컬럼을 추가해야 한다

 

반응형

 

4 IF의 세 번째 값을 0으로 두는 이유

IF()의 세 번째 인자는 조건이 거짓일 때 반환할 값이다. 조건부 집계에서는 보통 0을 넣는다. 그래야 조건에 맞지 않는 행이 합계에 영향을 주지 않으면서도 결과 타입이 숫자로 유지된다.

-- 봄이 아닌 행은 합계에서 제외되도록 0 처리 SUM(IF(season='봄', amount, 0)) AS '봄'

거짓일 때 문자열이나 빈 값을 넣으면 숫자 집계가 암묵 변환에 의존하게 된다. 집계 컬럼은 숫자로 일관되게 유지하는 편이 안전하다.

5 CASE WHEN으로도 같은 결과를 만들 수 있다

표준 SQL에 더 가까운 표현은 SUM(CASE WHEN ... THEN ... ELSE ... END)이다. MySQL에서는 IF()가 짧고 편하지만, 여러 DB로 옮길 가능성이 있다면 CASE 방식도 알아두는 편이 좋다.

SELECT uName, SUM(CASE WHEN season='봄' THEN amount ELSE 0 END) AS '봄' FROM pivotTest GROUP BY uName;

SUM(IF())

MySQL에서 짧고 읽기 쉽다. 간단한 조건부 집계에 적합하다.

SUM(CASE WHEN)

표준 SQL에 가까워 이식성이 좋다. 조건이 복잡해질 때 구조가 더 명확하다.

6 계절 값이 NULL이거나 오타가 있으면 어떻게 되는가

피벗 쿼리는 조건 문자열에 의존한다. season='봄'이라고 썼는데 데이터에는 '봄 '처럼 공백이 붙어 있거나 NULL이 들어 있으면 그 행은 어떤 계절 열에도 잡히지 않는다.

SELECT season, COUNT(*), SUM(amount) FROM pivotTest GROUP BY season; -- 피벗 전에는 원본 분류값을 먼저 점검한다.

리포트 쿼리를 만들기 전에 원본 분류값 목록을 먼저 확인하면, 오타·공백·NULL 때문에 합계가 맞지 않는 문제를 빨리 찾을 수 있다.

7 실무 활용 패턴

조건부 집계는 판매 리포트, 출석 현황, 주문 상태별 집계, 월별 통계처럼 "분류값을 열로 펼쳐야 하는" 거의 모든 보고서 쿼리에 응용할 수 있다.

업무 상황	GROUP BY 대상	IF 조건	결과 열
계절별 판매량	uName	season='봄'	봄 판매량
주문 상태별 건수	user_id	status='paid'	결제완료 건수
월별 매출	product_id	MONTH(order_date)=1	1월 매출
출석 현황	student_id	attendance='Y'	출석 횟수

8 자주 하는 실수

실수	증상	해결
GROUP BY를 빼먹음	전체 데이터가 한 줄로 합산되어 사람별 결과가 나오지 않는다	결과 행의 기준 컬럼을 GROUP BY에 넣는다
IF의 거짓값을 NULL로 둠	데이터 상태에 따라 합계가 예상보다 비거나 헷갈린다	조건부 합계에서는 보통 0을 사용한다
분류 문자열 오타	특정 열의 합계가 0으로 나온다	GROUP BY season으로 원본 분류값을 먼저 확인한다
별칭을 빼먹음	결과 컬럼명이 길고 읽기 어려워진다	AS '봄'처럼 화면용 컬럼명을 붙인다
새 분류값을 쿼리에 반영하지 않음	새 계절·상태 데이터가 합계에서 빠진다	분류값이 늘어날 때 SELECT 컬럼도 함께 추가한다

9 점검 순서

1

원본 데이터 확인SELECT * FROM pivotTest로 행 구조를 먼저 본다.

2

분류값 목록 확인SELECT season, COUNT(*) GROUP BY season으로 계절 이름을 확인한다.

3

조건부 컬럼 하나 테스트봄 열 하나만 먼저 만들고 결과가 맞는지 본다.

4

나머지 열 복제검증된 패턴을 여름, 가을, 겨울로 확장한다.

10 확장 예시 — 건수와 금액을 같이 보고 싶을 때

실무 리포트에서는 금액 합계만 보는 경우보다 건수와 금액을 함께 보는 경우가 많다. 이때도 원리는 같다. 값의 합계는 SUM(IF(..., amount, 0)), 조건에 맞는 행의 개수는 SUM(IF(..., 1, 0))로 만든다.

SELECT uName, SUM(IF(season='봄', 1, 0)) AS '봄_건수', SUM(IF(season='봄', amount, 0)) AS '봄_금액', SUM(IF(season='여름', 1, 0)) AS '여름_건수', SUM(IF(season='여름', amount, 0)) AS '여름_금액' FROM pivotTest GROUP BY uName;

목적	패턴	설명
조건별 금액 합계	SUM(IF(조건, amount, 0))	조건에 맞는 행의 금액만 더한다
조건별 행 개수	SUM(IF(조건, 1, 0))	조건에 맞으면 1점씩 더해 건수를 만든다
조건별 평균	SUM(IF(...amount...)) / NULLIF(SUM(IF(...1...)),0)	합계와 건수를 따로 만든 뒤 나눈다
전체 대비 비율	조건부 합계 / 전체 합계	분모가 0일 수 있으면 NULLIF로 방어한다

건수를 만들 때 COUNT(IF(...))를 무심코 쓰면 NULL 처리 방식 때문에 헷갈릴 수 있다. 초보 단계에서는 SUM(IF(조건, 1, 0))가 의도가 가장 분명하다.

11 리포트 쿼리 작성 체크리스트

1

행 기준 결정결과표에서 한 줄이 무엇을 의미하는지 먼저 정한다. 사람별, 상품별, 월별 중 하나가 기준이 된다.

2

열 기준 결정가로로 펼칠 분류값을 정한다. 계절, 상태, 월, 등급 같은 값이 여기에 해당한다.

3

집계값 결정금액을 더할지, 건수를 셀지, 평균을 낼지 결정한다.

4

누락 분류 확인원본 데이터에 새 분류값이 생겼는데 SELECT 열에 빠져 있지 않은지 확인한다.

12 성능과 유지보수 메모

피벗 쿼리는 읽기 편한 리포트를 만들지만, 분류값이 계속 늘어나는 데이터에는 SELECT 절이 길어지는 단점이 있다. 고정된 계절·상태·등급처럼 값의 종류가 안정적일 때 가장 잘 맞는다.

확인 항목	권장 판단	이유
분류값이 고정인가?	고정이면 SUM(IF) 적합	열 목록을 미리 정할 수 있다
분류값이 계속 늘어나는가?	애플리케이션 피벗 검토	SQL을 계속 수정해야 할 수 있다
행 수가 많은가?	GROUP BY 컬럼 인덱스 확인	묶는 기준이 느리면 전체 리포트가 느려진다
조건 컬럼이 문자열인가?	오타·공백 정규화	문자열이 다르면 다른 분류로 집계된다

리포트 쿼리는 정확성이 먼저다. 원본 합계와 피벗 결과의 전체 합계가 같은지 마지막에 반드시 대조하면 누락된 분류값을 빠르게 찾을 수 있다.

-- 검증용 합계 비교 SELECT SUM(amount) FROM pivotTest; SELECT SUM(봄 + 여름 + 가을 + 겨울) FROM ( SELECT SUM(IF(season='봄', amount, 0)) AS 봄, SUM(IF(season='여름', amount, 0)) AS 여름, SUM(IF(season='가을', amount, 0)) AS 가을, SUM(IF(season='겨울', amount, 0)) AS 겨울 FROM pivotTest GROUP BY uName ) p;

핵심 요약

SUM(IF(조건, 값, 0))조건을 만족하는 행만 합계에 참여시키는 조건부 집계 패턴

GROUP BY결과 행을 어떤 기준으로 나눌지 결정한다. 이 예제에서는 사람 이름이 기준이다.

피벗 결과행으로 저장된 분류값을 열로 펼쳐 리포트에 맞는 표를 만든다.

CASE WHEN 대안MySQL IF보다 길지만 표준 SQL에 가까워 이식성과 복잡한 조건에서 유리하다.

Tags

#MySQL #SUMIF #피벗테이블 #조건부집계 #GROUPBY #IF함수 #집계함수 #리포트쿼리 #통계쿼리 #SQL #데이터베이스 #DB #웹개발 #티스토리

티스토리 태그 입력란 복사용

MySQL, SUM IF, 피벗테이블, 조건부집계, GROUP BY, IF 함수, 집계함수, SQL, 데이터베이스, DB, 리포트쿼리, 통계쿼리, 웹개발, 티스토리