데이터베이스 질문과 답변
| section | sum_yn | a | b |
| A | Y | 1 | 2 |
| A | Y | 2 | 3 |
| A | N | 3 | 1 |
| A합계 | 3 | 5 | |
| B | N | 4 | 2 |
| B | Y | 5 | 3 |
| B | Y | 6 | 4 |
| B합계 | 11 | 7 | |
| 총합 | 14 | 12 |
위 표와 같이 section으로 구분하고 sum_yn = 'Y'인 것들만 a칼럼, b칼럼에 대해서 subtotal을 구하고, 마지막 row에는 총합을 구할수 있는 방법이 있나요?
고수님들의 답변 부탁드립니다.
by 마농
[2009.12.24 15:16:15]
WITH t AS
(
SELECT 'A' section, 'Y' sum_yn, 1 a, 2 b FROM dual
UNION ALL SELECT 'A', 'Y', 2, 3 FROM dual
UNION ALL SELECT 'A', 'N', 3, 1 FROM dual
UNION ALL SELECT 'B', 'N', 4, 2 FROM dual
UNION ALL SELECT 'B', 'Y', 5, 3 FROM dual
UNION ALL SELECT 'B', 'Y', 6, 4 FROM dual
)
SELECT section || DECODE(GROUPING_ID(section, sum_yn),1,'합계',3,'총계') section
, sum_yn
, DECODE(GROUPING(rownum),0,SUM(a),SUM(DECODE(sum_yn,'Y',a))) a
, DECODE(GROUPING(rownum),0,SUM(b),SUM(DECODE(sum_yn,'Y',b))) b
FROM t
GROUP BY ROLLUP(section, (sum_yn, ROWNUM))
;
(
SELECT 'A' section, 'Y' sum_yn, 1 a, 2 b FROM dual
UNION ALL SELECT 'A', 'Y', 2, 3 FROM dual
UNION ALL SELECT 'A', 'N', 3, 1 FROM dual
UNION ALL SELECT 'B', 'N', 4, 2 FROM dual
UNION ALL SELECT 'B', 'Y', 5, 3 FROM dual
UNION ALL SELECT 'B', 'Y', 6, 4 FROM dual
)
SELECT section || DECODE(GROUPING_ID(section, sum_yn),1,'합계',3,'총계') section
, sum_yn
, DECODE(GROUPING(rownum),0,SUM(a),SUM(DECODE(sum_yn,'Y',a))) a
, DECODE(GROUPING(rownum),0,SUM(b),SUM(DECODE(sum_yn,'Y',b))) b
FROM t
GROUP BY ROLLUP(section, (sum_yn, ROWNUM))
;
by finecomp
[2009.12.24 15:17:23]
a만...나머지는 직접...^^;
SELECT
DECODE(GROUPING(rn), 0, SUM(a)
, SUM(DECODE(sum_yn, 'Y', a))
) AS a
FROM( SELECT a.*, ROWNUM AS rn FROM t )
GROUP BY ROLLUP(section, rn)
SELECT
DECODE(GROUPING(rn), 0, SUM(a)
, SUM(DECODE(sum_yn, 'Y', a))
) AS a
FROM( SELECT a.*, ROWNUM AS rn FROM t )
GROUP BY ROLLUP(section, rn)
) 버튼을 클릭하여 작성 하시면 됩니다.