ORACLE & MSSQL | 그룹 별 소계 조회 - ROLLUP, UNION ALL

 

그룹 별 소계를 계산할 때 ROLLUP 과 UNION ALL을 사용한다.

 

나는 오라클SQL 로 SQL을 배워서 ROLLUP을 사용할 때는

// ORACLE SQL
GROUP BY ROLLUP(컬럼명)

이렇게 쓰는 줄만 알았다.

생각없이 MSSQL로도 저렇게 썼더니 안되길래 MSSQL은 ROLLUP을 못 쓰나 했지만

 

당연히 MSSQL도 ROLLUP을 쓸 수 있다. 폼이 다를 뿐.

// MSSQL
GROUP BY 컬럼명
 WITH ROLLUP

이렇게하면 원하는 컬럼별로 소계를 나타낼 수 있다.

 

실행해보면 소계가 잘 나오는데 행의 항목이 NULL로 나올 것이다.

예쁘게 '소계', '총계'를 써주고 싶은 마음이 든다.

그러려면 특정 행이 집계 레코드인지 판단을 해야한다.

 

해당 레코드가 집계 레코드인지 아닌지 구분할 때, GROUPING 함수를 사용한다.

// GROUPING 함수 
// ORACLE SQL
SELECT DNAME,	
       GROUPING(DNAME),
       JOB,      
       GROUPING(JOB),
       COUNT(*)	TOTAL EMP
  FROM EMP, DEPT 
 WHERE DEPT.DEPTNO = EMP.DEPTNO
 GROUP BY ROLLUP(DNAME, JOB)
 
 // MSSQL
SELECT DNAME,	
       GROUPING(DNAME),
       JOB,      
       GROUPING(JOB),
       COUNT(*)	TOTAL EMP
  FROM EMP, DEPT 
 WHERE DEPT.DEPTNO = EMP.DEPTNO
 GROUP BY DNAME, JOB 
  WITH ROLLUP

해당 레코드가 집계 레코드가 아닌 경우(기존의 데이터) : GROUPING 결과 = 0

해당 레코드가 집계 레코드일 경우(소계)                    : GROUPING 결과 = 1

 

 

 

이를 바탕으로 원하는 문자열을 지정할 수 있다.

 

보통 CASE문을 사용하는데

오라클SQL은 DECODE 함수로 더 간단하게 표현 가능하다.

// 소계를 나타내는 필드에 원하는 문자열 지정

// ORACLE SQL (DECODE)
SELECT DECODE(GROUPING(DNAME), 1, '총계', DNAME) AS DNAME	
       DECODE(GROUPING(JOB),   1, '소계', JOB)   AS JOB 
       COUNT(*)	TOTAL EMP
  FROM EMP, DEPT 
 WHERE DEPT.DEPTNO = EMP.DEPTNO
 GROUP BY ROLLUP(DNAME, JOB)


// MSSQL (CASE)
SELECT CASE GROUPING(DNAME) WHEN 1 THEN '총계' ELSE DNAME END AS DNAME,	
       CASE GROUPING(JOB)   WHEN 1 THEN '소계' ELSE JOB   END AS JOB,
       COUNT(*)	TOTAL EMP
  FROM EMP, DEPT 
 WHERE DEPT.DEPTNO = EMP.DEPTNO
 GROUP BY DNAME, JOB 
  WITH ROLLUP

 

추가적으로,

ROLLUP을 이용하지 않고 소계를 조회하는 방법으로 UNION ALL을 사용해보았다.

 

// UNION ALL로 소계 조회
SELECT P.PRODUCTNAME
     , C.CUSTOMERNAME
     , SUM(D.QUANTITY)
  FROM PRODUCTS P, ORDERDETAILS D, ORDERS O, CUSTOMERS C
 WHERE P.PRODUCTID  = D.PRODUCTID
   AND D.ORDERID    = O.ORDERID
   AND O.CUSTOMERID = C.CUSTOMERID
   AND D.QUANTITY > 70
 GROUP BY P.PRODUCTNAME, C.CUSTOMERNAME

UNION ALL

SELECT P.PRODUCTNAME
     , 'SUBTOTAL'
     , SUM(D.QUANTITY)
  FROM PRODUCTS P, ORDERDETAILS D, ORDERS O, CUSTOMERS C
 WHERE P.PRODUCTID  = D.PRODUCTID
   AND D.ORDERID    = O.ORDERID
   AND O.CUSTOMERID = C.CUSTOMERID
   AND D.QUANTITY > 70
 GROUP BY P.PRODUCTNAME
 
UNION ALL

SELECT 'TOTAL'
     , ''
     , SUM(D.QUANTITY)
  FROM PRODUCTS P, ORDERDETAILS D, ORDERS O, CUSTOMERS C
 WHERE P.PRODUCTID  = D.PRODUCTID
   AND D.ORDERID    = O.ORDERID
   AND O.CUSTOMERID = C.CUSTOMERID
   AND D.QUANTITY > 70
   
 ORDER BY SUM(D.QUANTITY), P.PRODUCTNAME

<실행결과>

 

테이블을 세로로 세 개 연달아 붙여주는 느낌으로 기존의 데이터 + 소계 + 총계 를 조회한다.

UNION의 경우 맨 처음에 작성한 SELECT문의 컬럼이 전체 UNION 한 테이블의 컬럼이 된다는 점을 활용한 것.

 

총계는 전체 통계이므로 GROUP BY 필요 없다.

그리고 마지막에 ORDER BY로 정렬해서 소계를 알맞은 곳에 끼워준다. 

이부분에서 경우에 따라 원하는 순서대로 정렬이 되지 않았다. 

예를 들어 SUM을 내림차순으로 하면서 총계는 가장 하단부에 위치하도록 하는 것을 못하겠다.

 

SELECT절에 SORT 컬럼을 생성하여 1, 2, 3 값을 주고 SORT를 정렬하는 방법도 있겠지만

굳이 SORT 컬럼을 보고 싶지 않고...

 

ROLLUP이라는 편리한 함수가 있으므로 적극적으로 사용하자!