MYSQL - ROLLUP 함수로 소계, 합계 한 번에 조회하기

현업에서는 데이터베이스에서 데이터를 조회하여 엑셀 보고서를 작성해야 하는 경우가 많습니다. 특히 금융권 같은 경우 소계, 합계 같은 통계를 보고서에 녹여야 할 때가 많기 때문에 MYSQL의 ROLLUP 함수를 활용하여 데이터를 조회한다면 이런 작업에 소요되는 시간을 단축하는 데에 큰 작용할 수 있습니다.

 

 

ROLLUP 함수란?

MYSQL의 WITH ROLLUP 함수는 합계를 구할 때 사용합니다. GROUP BY 절을 함께 사용하여 소계 및 총계를 쉽게 구할 수 있습니다. 그리고 기존 데이터에 소계와 총계를 그룹별로 추가하여 보여주고 그룹핑한 컬럼은 NULL 값으로 채워서 보여줍니다.

 

• SELECT 그룹 컬럼, SUM(대상 컬럼) FROM 테이블명 GROUP BY 그룹 컬럼 WITH ROLLUP;

SELECT DEPT, TEAM, SUM(SALARY) FROM SALARY GROUP BY DEPT, TEAM WITH ROLLUP;

소계 및 총계 결과

 

 

NULL 값 채우기 - COALESCE, GROUPING

ROLLUP 함수로 소계와 총계를 집계하면 그룹핑한 컬럼은 NULL 값으로 보여주기 때문에 COALESCE 또는 GROUPING 함수를 활용하여 NULL 값을 원하는 그룹명으로 채울 수 있습니다.

 

 1) COALESCE

• SELECT COALESCE(그룹 컬럼, '합계') , SUM(대상 컬럼) FROM 테이블명 GROUP BY 그룹 컬럼 WITH ROLLUP;

SELECT COALESCE(DEPT, '총계') AS DEPT,
       COALESCE(TEAM, '소계') AS TEAM,
       SUM(SALARY) 
FROM SALARY GROUP BY DEPT, TEAM WITH ROLLUP;

 

 2) GROUPING

• SELECT IF(GROUPING(그룹컬럼), '합계', 그룹 컬럼), SUM(대상 컬럼) FROM 테이블명 GROUP BY 그룹 컬럼 WITH ROLLUP;

※ 꿀팁

GROUOPING은 0 또는 1을 반환하기 때문에 IF와 함께 쓰면 COALESCE와 같은 결과를 얻을 수 있음!!

SELECT IF(GROUPING(DEPT), '총계', DEPT) AS DEPT,
       IF(GROUPING(TEAM), '소계', TEAM) AS TEAM,
       SUM(SALARY) 
FROM SALARY GROUP BY DEPT, TEAM WITH ROLLUP;

 

COALESCE와 GROUPING 결과 값은 아래와 같이 같은 결과로 조회할 수 있습니다.

COALESCE 또는 GROUPING 결과