[Sql] Oracle - ROLLUP 과 GROUPING :: Experience Never Fail !!

[Sql] Oracle - ROLLUP 과 GROUPING

Sort 2010. 12. 22. 14:59

2010. 12. 22. 14:59

[출처] [ORACLE] ROLLUP 과 GROUPING|작성자 소토로

http://blog.naver.com/ssari93?Redirect=Log&logNo=120020259608

invalid-file

데이터

비용코드	TB_MSTCODE
MST_CD	MST_NM
A00001	하드웨어
A00002	소프트웨어

서브비용코드	TB_SUBCODE
SUB_CD	SUB_NM
B00001	사용료
B00002	관리비

년도별비용	TB_YEARCOST
MST_CD	SUB_CD	YEAR	COST
A00001	B00001	2002	10000
A00001	B00001	2003	20000
A00001	B00001	2004	30000
A00001	B00001	2005	40000
A00001	B00002	2002	15000
A00001	B00002	2003	25000
A00001	B00002	2004	35000
A00001	B00002	2005	45000
A00002	B00001	2002	16000
A00002	B00001	2003	26000
A00002	B00001	2004	36000
A00002	B00001	2005	46000
A00002	B00002	2002	17000
A00002	B00002	2003	27000
A00002	B00002	2004	37000
A00002	B00002	2005	47000

년도별 비용 쿼리

SELECT D.MST_NM
   , D.SUB_NM
   , SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
   , SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
   , SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
   , SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
FROM (SELECT MST_CD
, MST_NM
, SUB_CD
, SUB_NM
   FROM (SELECT MST_CD
   , MST_NM
FROM TB_MSTCODE) A
, (SELECT SUB_CD
   , SUB_NM
FROM TB_SUBCODE) B ) D
   , TB_YEARCOST E
WHERE D.MST_CD = E.MST_CD(+)
   AND D.SUB_CD = E.SUB_CD(+)
GROUP BY D.MST_NM
   , D.SUB_NM
ORDER BY D.MST_NM
   , D.SUB_NM

결과

년도별 비용
MST_NM	SUB_NM	2002년	2003년	2004년	2005년
소프트웨어	관리비	17000	27000	37000	47000
소프트웨어	사용료	16000	26000	36000	46000
하드웨어	관리비	15000	25000	35000	45000
하드웨어	사용료	10000	20000	30000	40000

아래와 같이 소계와 합계를 나타내고자 한다면

소계 및 합계
MST_NM	SUB_NM	2002년	2003년	2004년	2005년
소프트웨어	관리비	17000	27000	37000	47000
	사용료	16000	26000	36000	46000
	소계	33000	53000	73000	93000
하드웨어	관리비	15000	25000	35000	45000
	사용료	10000	20000	30000	40000
	소계	25000	45000	65000	85000
합계		58000	98000	138000	178000

방법1

SELECT D.MST_NM
   , D.SUB_NM
   , SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
   , SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
   , SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
   , SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
FROM (SELECT MST_CD
, MST_NM
, SUB_CD
, SUB_NM
   FROM (SELECT MST_CD
   , MST_NM
FROM TB_MSTCODE) A
, (SELECT SUB_CD
   , SUB_NM
FROM TB_SUBCODE) B ) D
   , TB_YEARCOST E
WHERE D.MST_CD = E.MST_CD(+)
   AND D.SUB_CD = E.SUB_CD(+)
GROUP BY ROLLUP(D.MST_NM
   , D.SUB_NM)
ORDER BY D.MST_NM
   , D.SUB_NM

결과1

MST_NM	SUB_NM	COST1	COST2	COST3	COST4
소프트웨어	관리비	17000	27000	37000	47000
소프트웨어	사용료	16000	26000	36000	46000
소프트웨어		33000	53000	73000	93000
하드웨어	관리비	15000	25000	35000	45000
하드웨어	사용료	10000	20000	30000	40000
하드웨어		25000	45000	65000	85000
		58000	98000	138000	178000

방법2

SELECT NVL(D.MST_NM, '합계') AS MST_NM
   , DECODE(D.MST_NM, NULL, '합계', NVL(D.SUB_NM, '소계')) AS SUB_NM
   , SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
   , SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
   , SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
   , SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
FROM (SELECT MST_CD
, MST_NM
, SUB_CD
, SUB_NM
   FROM (SELECT MST_CD
   , MST_NM
FROM TB_MSTCODE) A
, (SELECT SUB_CD
   , SUB_NM
FROM TB_SUBCODE) B ) D
   , TB_YEARCOST E
WHERE D.MST_CD = E.MST_CD(+)
   AND D.SUB_CD = E.SUB_CD(+)
GROUP BY ROLLUP(D.MST_NM
   , D.SUB_NM)
ORDER BY D.MST_NM
   , D.SUB_NM

결과2

MST_NM	SUB_NM	COST1	COST2	COST3	COST4
소프트웨어	관리비	17000	27000	37000	47000
소프트웨어	사용료	16000	26000	36000	46000
소프트웨어	소계	33000	53000	73000	93000
하드웨어	관리비	15000	25000	35000	45000
하드웨어	사용료	10000	20000	30000	40000
하드웨어	소계	25000	45000	65000	85000
합계	합계	58000	98000	138000	178000

방법3

SELECT DECODE(GROUPING(D.MST_NM), 1, '합계', D.MST_NM) AS MST_NM
   , DECODE(GROUPING(D.MST_NM), 1, '합계',
   DECODE(GROUPING(D.SUB_NM), 1, '소계', D.SUB_NM)) AS SUB_NM
   , SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
   , SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
   , SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
   , SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
FROM (SELECT MST_CD
, MST_NM
, SUB_CD
, SUB_NM
   FROM (SELECT MST_CD
   , MST_NM
FROM TB_MSTCODE) A
, (SELECT SUB_CD
   , SUB_NM
FROM TB_SUBCODE) B ) D
   , TB_YEARCOST E
WHERE D.MST_CD = E.MST_CD(+)
   AND D.SUB_CD = E.SUB_CD(+)
GROUP BY ROLLUP(D.MST_NM
   , D.SUB_NM)
ORDER BY D.MST_NM
   , D.SUB_NM

결과3

MST_NM	SUB_NM	COST1	COST2	COST3	COST4
소프트웨어	관리비	17000	27000	37000	47000
소프트웨어	사용료	16000	26000	36000	46000
소프트웨어	소계	33000	53000	73000	93000
하드웨어	관리비	15000	25000	35000	45000
하드웨어	사용료	10000	20000	30000	40000
하드웨어	소계	25000	45000	65000	85000
합계	합계	58000	98000	138000	178000

※ 참고 : GROUPING 함수는 그룹핑 값이 NULL 이면 1을 리턴한다.

방법4

SELECT DECODE(GROUPING(D.MST_NM), 1, '합계', D.MST_NM) AS MST_NM
   , DECODE(GROUPING(D.MST_NM), 1, '합계',
   DECODE(GROUPING(D.SUB_NM), 1, '소계', D.SUB_NM)) AS SUB_NM
   , SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
   , SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
   , SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
   , SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
FROM (SELECT MST_CD
, MST_NM
, SUB_CD
, SUB_NM
   FROM (SELECT MST_CD
   , MST_NM
FROM TB_MSTCODE) A
, (SELECT SUB_CD
   , SUB_NM
FROM TB_SUBCODE) B ) D
   , TB_YEARCOST E
WHERE D.MST_CD = E.MST_CD(+)
   AND D.SUB_CD = E.SUB_CD(+)
GROUP BY GROUPING SETS (
(D.MST_NM, D.SUB_NM)
   , (D.MST_NM)
   , ())
ORDER BY D.MST_NM
   , D.SUB_NM

결과4

MST_NM	SUB_NM	COST1	COST2	COST3	COST4
소프트웨어	관리비	17000	27000	37000	47000
소프트웨어	사용료	16000	26000	36000	46000
소프트웨어	소계	33000	53000	73000	93000
하드웨어	관리비	15000	25000	35000	45000
하드웨어	사용료	10000	20000	30000	40000
하드웨어	소계	25000	45000	65000	85000
합계	합계	58000	98000	138000	178000

'DataBase > Sql' 카테고리의 다른 글

[Sql] Oracle, Mssql, Mysql - 설치및 사용 팁 (0)	2011.02.17
[Sql] Oracle - 숫자여부 체크 (0)	2011.02.12
[Sql] Oracle - Function 사용하기 (0)	2010.11.16
[Sql] Oracle - Sequence 사용하기 (0)	2010.11.10
[Sql] Oracle - distinct, rowid 중복제거, 최신 데이터 하나만 가져오기 (0)	2010.11.03

Experience Never Fail !!

[Sql] Oracle - ROLLUP 과 GROUPING

'DataBase > Sql' 카테고리의 다른 글

+ Recent posts

티스토리툴바