[MSSQL] ROLLUP 사용법 (소계, 합계)

SQL Server에서 그룹 함수로 집계된 데이터에서 소계, 합계를 쉽게 구하기 위해서는 ROLLUP를 사용하면 된다. 아래의 예제를 참고하면 쉽게 이해할 수 있다.

 

ROLLUP은 2가지 사용방법이 있다. 어느 것을 사용하든 결과는 동일하다.

 

방법1 : GROUP BY ROLLUP(그룹컬럼) 방법2 : GROUP BY 그룹컬럼 WITH ROLLUP

 

방법1 : GROUP BY ROLLUP(그룹컬럼)

SELECT job
     , deptno
     , SUM(sal) AS total_sal
  FROM emp
 WHERE job IN ('MANAGER', 'CLERK', 'SALESMAN')
 GROUP BY ROLLUP(job, deptno) 

 

 

그룹 컬럼으로 지정된 모든 컬럼에 대하여 소계와 합계를 집계한다.

 

방법2 : GROUP BY 그룹컬럼 WITH ROLLUP

SELECT job
     , deptno
     , SUM(sal) AS total_sal
  FROM emp
 WHERE job IN ('MANAGER', 'CLERK', 'SALESMAN')
 GROUP BY job, deptno
  WITH ROLLUP

 

 

GROUPING 함수의 이해

 

GROUPING 함수를 이해하고 있으면 ROLLUP을 더욱더 효과적으로 사용할 수 있다.

 

GROUPING 함수는 집계된 행의 NULL을 판단할 때 사용한다. 실제 조회된 데이터의 NULL과 소계, 합계로 추가된 NULL은 GROUPING 함수만 구별이 가능하다.

 

소계, 합계인 경우 1을 리턴하고 실제 데이터가 집계된 행(컬럼값이 NULL인 경우도)은 0을 리턴한다.

 

소계 제거 방법

SELECT job
     , deptno
     , SUM(sal) AS total_sal
  FROM emp
 WHERE job IN ('MANAGER', 'CLERK', 'SALESMAN')
 GROUP BY ROLLUP(job, deptno)  
HAVING GROUPING(job) = 1 OR GROUPING(deptno) = 0 

 

 

HAVING 절에 GROUPING 함수를 사용하여 소계 출력 조건을 부여할 수 있다.

WITH ROLLUP를 사용할 경우 WITH ROLLUP HAVING~로 사용하면 된다. 

 

합계 출력 : GROUPING(job) = 1 

소계 제외 : GROUPING(deptno) = 0 

 

소계, 합계 텍스트 표시

SELECT CASE WHEN GROUPING(job) = 1 THEN '합계'
            ELSE job
	   END AS job
     , CASE WHEN GROUPING(deptno) = 1 THEN '소계'
            ELSE CAST(deptno AS nvarchar)
	   END AS deptno
     , SUM(sal) AS total_sal
  FROM emp
 WHERE job IN ('MANAGER', 'CLERK', 'SALESMAN')
 GROUP BY ROLLUP(job, deptno)  

 

 

GROUPING 함수를 사용하여 소계, 합계 행인지 판단하여 텍스트를 표시

 

CASE WHEN jog IS NULL THEN '합계' 처럼 사용할 수 있으나 job 컬럼에 NULL 값이 포함되어 있으면 잘못 표시 될 수 있으므로 GROUPING 함수를 사용할 것을 권장한다.

 

job 컬럼에 NULL이 절대 없을 경우는 ISNULL(job, '합계')로 사용하면 더욱 간편하다.

 

 

SQL Server 2012 버전 부터 사용가능한 IIF 함수를 이용하면 쿼리문을 조금더 단순하게 만들 수 있다.

 

예제쿼리(테이블뷰포함).txt

0.00MB

[MSSQL] CUBE 함수 사용법 (모든 경우의 합계)