MSSQL ROLLUP

---

안녕하세요. 따시입니다.

 

MSSQL 에서  ROLLUP 을 소계 하려고 합니다.

 

예시로 테이블은 3개로 되어 있습니다.

MSSQL 샘플 데이타는 한동안 계속 아래로 이용할꺼 같습니다.

 

ITEM_MASTER TABLE (상품 마스터정보)

ITEM_CODE (상품코드)	ITEM_NAME (상품명)	ITEM_PRICE (상품가격)
M001	콜라	1,000
M002	사이다	1,000
P001	피자	10,000
P002	피자세트(콜라포함)	10,500
C001	후라이드치킨	8,000
C002	양념치킨	9,000

MEMBER_INFO TABLE (회원정보)

USER_ID (회원아이디)	USER_NAME (회원이름)
ID0001	철수
ID0002	영희
ID0003	광수
ID0004	명수
ID0005	길동
ID0006	수영

MEMBER_ORDER TABLE (회원주문 정보)

USER_ID (회원아이디)	ITEM_CODE (상품코드)	ORDER_DATE (주문일자)	AMOUNT (주문수량)
ID0001	M002	2020-07-01	5
ID0001	M001	2020-07-01	3
ID0002	P001	2020-07-01	1
ID0002	M001	2020-07-02	1
ID0004	C002	2020-07-02	1
ID0006	C001	2020-07-02	1
ID0001	C001	2020-07-03	1
ID0003	P002	2020-07-03	1
ID0006	P001	2020-07-03	2
ID0002	C002	2020-07-03	2

 

 

먼저 회원별 구매 건수 합산을 조회해 보겠다

SELECT 
	B.USER_NAME,            -- 회원명
	SUM(A.AMOUNT) AS AMOUNT -- 주문수량
FROM MEMBER_ORDER A, MEMBER_INFO B
WHERE 1=1
	AND A.USER_ID = B.USER_ID
GROUP BY B.USER_NAME

 

ROLLUP 함수 사용 - GroupBy 컬럼 기준으로 총계를 구함

ROLLUP 을 사용하여 총합계의 값을 구한다.

SELECT 
	B.USER_NAME,             -- 회원명
	SUM(A.AMOUNT) AS AMOUNT  -- 주문수량
FROM MEMBER_ORDER A, MEMBER_INFO B
WHERE 1=1
	AND A.USER_ID = B.USER_ID
GROUP BY  B.USER_NAME
WITH ROLLUP

이렇게 하면 맨 아래에 AMOUNT은 총 합계의 18 값이 나온다.

그러나 USER_NAME은 NULL로 표시가 된다.

 

이러면 어떻게 해야할가.

개발하면서 뷰페이지에서 따로 수정없이 합계 값도 한번에 출력하고 싶다.

 

이럴 결우에는 GROUPING 함수를 써서 구분 할 수가 있다.

SELECT 
	GROUPING(B.USER_NAME) AS GROUPING,  -- GROUPING
	B.USER_NAME,                        -- 회원명
	SUM(A.AMOUNT) AS AMOUNT             -- 주문수량
FROM MEMBER_ORDER A, MEMBER_INFO B
WHERE 1=1
	AND A.USER_ID = B.USER_ID
GROUP BY  B.USER_NAME
WITH ROLLUP

GROUPING(B.USER_NAME) 컬럼을 추가 하였다. 

위에 실행 결과를 보면 GROUPING 컬럼에 0, 1로 구분되어 출력이 된다.

ROLLUP으로 합계가 된 ROW에는 1값이 출력된다.

 

그렇다면.. CASE 문을 사용하여 깔끔하게 출력을 해보자.

SELECT 
	(CASE WHEN GROUPING(B.USER_NAME) = 1 
              THEN '총합계'
              ELSE B.USER_NAME END) AS USER_NAME,  -- 회원명
	SUM(A.AMOUNT) AS AMOUNT                    -- 주문수량
FROM MEMBER_ORDER A, MEMBER_INFO B
WHERE 1=1
	AND A.USER_ID = B.USER_ID
GROUP BY  B.USER_NAME
WITH ROLLUP

CASE WHEN을 사용하여 GROUPING(B.USER_NAME) 값이 1일 경우에는 '총합계'를 출력하고

ELSE 그 외에 것들은 USER_NAME을 출력하게 하였다.

이렇게 한다면 위와 같이 출력이 된다.

통계 페이지를 만들때에 사용 할 경우 SQL 쿼리로 한번에 데이타를 출력하여 편리함이 있다.

 

 

---