MySQL WITH ROLLUP — 소계·합계 자동 계산 완전 정리

 

 

Web Development · MySQL

MySQL WITH ROLLUP —
소계·합계 자동 계산 완전 정리

GROUP BY + WITH ROLLUP · 소계행 · 전체합계행 · GROUPING() 함수

시작 오늘 분석할 코드

-- 그룹별 소계와 전체합계를 자동으로 추가 SELECT num, groupName, SUM(price * amount) AS '비용' FROM buytbl GROUP BY groupName, num WITH ROLLUP; -- 결과: 각 num별 소계 + groupName별 소계 + 전체 합계 행 자동 생성

WITH ROLLUP을 GROUP BY 끝에 붙이면 각 그룹 단계마다 소계행과 최종 전체합계 행이 자동으로 추가된다. 소계/합계 행에서 집계된 컬럼은 NULL로 표시된다.

---

1 WITH ROLLUP 기본 개념

WITH ROLLUP

GROUP BY 뒤에 붙여 그룹 단계별 소계와 전체 합계 행을 자동 생성

소계행 (subtotal)

각 그룹(groupName)이 끝날 때 해당 그룹의 합계가 자동 삽입됨

전체합계행 (grand total)

결과 맨 마지막에 전체 SUM 행 1개 자동 추가 — NULL NULL 합계

NULL의 의미

ROLLUP 행에서 NULL은 "이 수준에서 집계됨"을 의미 — 실제 NULL과 다름

-- GROUP BY 1단계: groupName별 합계 SELECT groupName, SUM(price*amount) AS 비용 FROM buytbl GROUP BY groupName WITH ROLLUP; -- 결과: 가전제품 120 / 의류 98 / 컴퓨터 450 / NULL 668 ← 합계 -- GROUP BY 2단계: groupName, num 복합 SELECT groupName, num, SUM(price*amount) FROM buytbl GROUP BY groupName, num WITH ROLLUP; -- 각 num별 행 → groupName 소계행(num=NULL) → 전체합계(groupName=NULL, num=NULL)

---

2 ROLLUP 결과 구조 이해

GROUP BY groupName, num WITH ROLLUP의 결과는 3단계 계층 구조를 가진다.

groupName	num	비용	행 종류
가전제품	3	75	일반 데이터행
가전제품	7	45	일반 데이터행
가전제품	NULL	120	groupName 소계행
컴퓨터	5	200	일반 데이터행
컴퓨터	NULL	200	groupName 소계행
NULL	NULL	320	전체 합계행

ROLLUP 소계행/합계행의 NULL은 실제 데이터의 NULL이 아니다. COALESCE()나 CASE 문으로 "소계" 또는 "합계" 텍스트로 바꿔주면 가독성이 높아진다.

---

3 ROLLUP 행 식별 — GROUPING() · COALESCE()

ROLLUP이 자동으로 추가한 행인지 실제 데이터인지 구분할 때 GROUPING(컬럼)을 사용한다. 소계/합계 행에서는 1, 일반 데이터 행에서는 0을 반환한다.

-- GROUPING()으로 소계행 식별 후 라벨 변환 SELECT CASE WHEN GROUPING(groupName) = 1 THEN '전체합계' ELSE groupName END AS 그룹, CASE WHEN GROUPING(num) = 1 THEN '소계' ELSE CAST(num AS CHAR) END AS 번호, SUM(price*amount) AS 비용 FROM buytbl GROUP BY groupName, num WITH ROLLUP; -- COALESCE로 NULL → '합계' 텍스트 (더 간단) SELECT COALESCE(groupName, '전체합계') AS 그룹, SUM(price*amount) AS 비용 FROM buytbl GROUP BY groupName WITH ROLLUP;

COALESCE(컬럼, '대체값')은 NULL이면 대체값, 아니면 원래 값을 반환한다. 단, 실제로 NULL 데이터가 있는 컬럼이라면 GROUPING()으로 ROLLUP 행과 구분해야 한다.

---

 

반응형

 

4 실습 시나리오 — buytbl 소계 보고서

buytbl의 구매 데이터를 그룹별·번호별로 집계하고, 그룹 소계와 전체합계를 한 번에 출력하는 전형적인 ROLLUP 시나리오다.

-- 기본 ROLLUP — 그룹별 합계 + 전체합계 SELECT COALESCE(groupName, '전체합계') AS 그룹, SUM(price*amount) AS 총비용, COUNT(*) AS 건수 FROM buytbl GROUP BY groupName WITH ROLLUP; -- 2단계 ROLLUP: groupName → num SELECT COALESCE(groupName, '전체합계') AS 그룹, COALESCE(CAST(num AS CHAR), '소계') AS 번호, SUM(price*amount) AS 비용 FROM buytbl GROUP BY groupName, num WITH ROLLUP; -- 결과: 가전제품/3 → 가전제품/소계 → 컴퓨터/5 → 컴퓨터/소계 → 전체합계

COALESCE를 num 컬럼에도 함께 쓸 때는 CAST(num AS CHAR)로 타입을 먼저 맞춰야 한다. num이 INT이면 COALESCE(num, '소계')는 타입 불일치 오류가 날 수 있다.

---

5 ROLLUP vs UNION ALL — 소계 구현 비교

방법	코드량	유지보수	적합한 경우
WITH ROLLUP	짧음	자동 (컬럼 변경에 강함)	GROUP BY 계층 소계가 표준 패턴일 때
UNION ALL + SUM	길음	수동 (쿼리 2개 유지)	소계 행 위치·형식을 정밀 제어할 때
WITH CTE + ROLLUP	중간	우수	ROLLUP 결과에 추가 필터가 필요할 때

---

6 WITH ROLLUP + HAVING — 서브쿼리 우회 패턴

HAVING은 ROLLUP이 만든 소계/합계 행에도 적용된다. ROLLUP 결과만 필터링하려면 서브쿼리로 감싸야 한다.

-- HAVING이 소계행까지 필터하는 문제 SELECT groupName, SUM(price*amount) FROM buytbl GROUP BY groupName WITH ROLLUP HAVING SUM(price*amount) > 100; -- 문제: 전체합계 행도 HAVING 조건 통과하면 결과에 포함됨 -- 서브쿼리로 감싸서 ROLLUP 후 필터 SELECT * FROM ( SELECT COALESCE(groupName, '전체합계') AS 그룹, SUM(price*amount) AS 비용 FROM buytbl GROUP BY groupName WITH ROLLUP ) AS rollup_result WHERE 비용 > 100; -- 외부 WHERE가 ROLLUP 결과를 필터 — 합계 행도 그냥 데이터 행으로 취급

ROLLUP + HAVING 조합은 의도치 않게 소계행이 제거될 수 있다. ROLLUP 결과를 인라인 뷰(서브쿼리)로 만든 후 외부 WHERE로 필터하는 것이 명확하고 안전하다.

---

7 자주 하는 실수

실수	증상	해결
ROLLUP 행의 NULL을 실제 NULL로 혼동	WHERE groupName IS NULL로 ROLLUP 행만 조회됨	GROUPING() 또는 COALESCE() 사용
HAVING + WITH ROLLUP 조합	소계행도 HAVING 조건 대상이 되어 제거됨	ROLLUP 후 외부 SELECT로 감싸 필터
GROUP BY 순서 바꿈	소계 단계 순서가 달라짐	가장 큰 집계 기준이 GROUP BY 앞에 와야
실제 NULL 데이터 혼동	COALESCE가 실제 NULL에도 '합계' 표시	GROUPING() 함수로 ROLLUP NULL과 구분

핵심 한 줄 요약

WITH ROLLUPGROUP BY 끝에 붙여 소계·합계 행 자동 추가

소계행각 groupName 그룹 끝에 그룹 합계 행 삽입 (num=NULL)

전체합계행결과 마지막에 전체 SUM 행 (groupName=NULL, num=NULL)

NULL 의미ROLLUP 행의 NULL = "이 레벨에서 집계됨" — 실제 NULL 아님

GROUPING(컬럼)ROLLUP 행=1, 일반 데이터=0 반환 — 행 종류 식별

COALESCE 활용COALESCE(groupName,'전체합계') → NULL을 텍스트 라벨로 교체

CASE WHEN GROUPINGCASE WHEN GROUPING(col)=1 THEN '소계' ELSE col END — 계층별 라벨

2단계 GROUP BYGROUP BY A, B WITH ROLLUP → B 소계 + A 소계 + 전체합계

적합한 사용처보고서 형태 데이터 — 부서별/분기별 매출 소계 포함 테이블

GROUP BY 순서 중요앞 컬럼이 큰 분류, 뒤 컬럼이 세부 분류 순으로 써야 의미 맞음

ROLLUP vs CUBEROLLUP=계층적 집계(부분집합), CUBE=모든 조합 집계 (MySQL 미지원)

MySQL 지원MySQL 5.0 이상에서 WITH ROLLUP 지원 — CUBE는 미지원

NULL 필터 주의WHERE groupName IS NULL → ROLLUP 합계행도 걸림 — 의도 확인 필요

실무 사용 목적별도 UNION ALL 없이 소계·합계 포함 보고서 한 쿼리로 생성

ROLLUP 행 수N 그룹 + 소계행N + 전체합계1 — 원본보다 더 많은 행 반환

GROUP BY A WITH ROLLUPA 그룹별 집계 + 마지막에 전체합계 1행 = GROUP BY 결과 + 1행

buytbl 예시GROUP BY groupName WITH ROLLUP → 가전/의류/컴퓨터 소계 + 전체합계

핵심 한 줄WITH ROLLUP = GROUP BY 결과에 계층별 소계·전체합계 행 자동 삽입

CAST(num AS CHAR)숫자 컬럼에 COALESCE 쓸 때 문자열로 타입 맞춰야 오류 없음

ROLLUP 행 인식법GROUP BY 컬럼이 NULL인 행 = ROLLUP 소계/합계 행

GROUPING() 반환값일반행=0, ROLLUP 소계/합계행=1 — 이 값으로 CASE 분기

ORDER BY + ROLLUPMySQL 8.0+ ORDER BY와 혼용 가능 — 8.0 미만은 제한 있음

WITH ROLLUP 위치GROUP BY 목록 맨 끝에 WITH ROLLUP 키워드 추가 — HAVING 앞

소계 없는 전체합계만GROUP BY 없이 집계하면 전체합계만 나옴 — ROLLUP 불필요

ROLLUP 실무 예월별 지역별 판매 소계 → GROUP BY region, month WITH ROLLUP

HAVING과 ROLLUPHAVING이 ROLLUP 합계행까지 필터 → 서브쿼리로 감싸 사용

ROLLUP 주의점NULL이 있는 원본 데이터가 있으면 소계행과 구분 어려움

GROUPING(A,B) MySQLMySQL 8.0+ GROUPING(A,B) 비트마스크 지원 — A=2, B=1

WITH ROLLUP과 UNIONROLLUP이 UNION ALL보다 코드 짧고 자동 — 간단한 보고서엔 ROLLUP

num 값 의미buytbl.num은 구매 번호 — num별 소계는 각 구매의 단가*수량

ROLLUP SUM 검증전체합계행 SUM = 모든 그룹 소계 합산 — 검산용으로 유용

buytbl 핵심 쿼리GROUP BY groupName, num WITH ROLLUP — 그룹×번호 소계+합계

서브쿼리 우회ROLLUP 결과에 필터 필요 → 인라인 뷰로 감싼 뒤 외부 WHERE

COALESCE 위치SELECT 절에서 사용 — GROUPING() 없이 NULL을 텍스트로 교체

소계행 추가 원리GROUP BY A, B → A 기준 그룹 끝마다 B=NULL 소계행 자동 삽입

ROLLUP SELECT 컬럼SELECT에 GROUP BY 기준 컬럼 + 집계함수만 — 다른 컬럼은 비결정적

외부 WHERE로 필터서브쿼리 롤업 결과를 WHERE 비용 > 100 — HAVING 없이 필터 가능

ROLLUP 핵심 구문GROUP BY 컬럼1, 컬럼2 WITH ROLLUP — 키워드만 붙이면 자동

소계 라벨 변환COALESCE + CASE WHEN GROUPING 조합으로 계층별 라벨 부여

보고서 활용엑셀 소계 기능처럼 SQL로 자동 소계 행 생성 가능

ROLLUP 요약GROUP BY + WITH ROLLUP = 계층 집계 + 소계 + 합계 한 방에

전체합계 위치ROLLUP 결과 맨 마지막 행 — 모든 그룹 소계를 합산한 값

NULL = 집계 기호ROLLUP의 NULL은 "집계된 레벨" 표시 — 데이터 빈 것과 다름

부서별 소계 예시GROUP BY dept, month WITH ROLLUP → 월→부서→전체 3단계 집계

1단계 ROLLUPGROUP BY groupName WITH ROLLUP → 그룹 행 + 전체합계 1행만 추가

Tags

#MySQL #ROLLUP #GROUP BY #소계 #합계 #WITH ROLLUP #집계함수 #NULL #SQL #데이터베이스 #웹개발 #티스토리

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

MySQL, ROLLUP, GROUP BY, 소계, 합계, WITH ROLLUP, 집계함수, NULL, SQL, 데이터베이스, 웹개발, 티스토리