데이터베이스 질문과 답변
| 반 | 이름 | 몸무게 | 학급비율 | 전체비율 |
| 1반 | 철수 | 50 | 50/110 | 50/210 |
| 1반 | 영희 | 60 | 60/110 | 60/210 |
| 소계 | 110 | |||
| 2반 | 호동 | 100 | 100/100 | 100/210 |
| 소계 | 100 | |||
| 합계 | 210 |
rollup으로 결과값을 이렇게 만들고 싶어요
다른거는 알겠는데 학급비율이랑 전체비율 이부분을 어떻게 표현해야 할지....;;
개인 몸무게를 소계 또는 전체 합계로 나눠서 표현하는 부분이거든요.
ㅠㅠ
by Kyle
[2016.10.25 18:05:13]
WITH T(CLASS, NAME, WEIGHT) AS (
SELECT '1반', '철수', 50 FROM DUAL UNION ALL
SELECT '1반', '영희', 60 FROM DUAL UNION ALL
SELECT '2반', '호동', 100 FROM DUAL
)
SELECT
DECODE(GROUPING(NAME), 0, CLASS, GROUPING(CLASS), '총계', '소계') CLASS,
NAME,
SUM(WEIGHT),
DECODE(GROUPING(NAME), 1, 0, ROUND(SUM(WEIGHT) / MAX(C_S) * 100, 2) ) C_R,
DECODE(GROUPING(NAME), 1, 0, ROUND(SUM(WEIGHT) / MAX(T_S) * 100, 2) ) T_R
FROM (
SELECT
CLASS,
NAME,
WEIGHT,
SUM(WEIGHT) OVER(PARTITION BY CLASS) C_S,
SUM(WEIGHT) OVER() T_S
FROM T
)
GROUP BY ROLLUP(CLASS, NAME)
by 마농
[2016.10.25 18:23:05]
WITH t AS ( SELECT '1반' cls, '철수' nm, 50 wgt FROM dual UNION ALL SELECT '1반', '영희', 60 FROM dual UNION ALL SELECT '2반', '호동', 100 FROM dual ) SELECT NVL(cls, '합계') cls , DECODE(gid, 0, nm, 1, '소계') nm , wgt , DECODE(gid, 0, ROUND(RATIO_TO_REPORT(wgt) OVER(PARTITION BY gid, cls)*100, 2)) c_r , DECODE(gid, 0, ROUND(RATIO_TO_REPORT(wgt) OVER(PARTITION BY gid )*100, 2)) t_r FROM (SELECT cls, nm , SUM(wgt) wgt , GROUPING_ID(cls, nm) gid FROM t GROUP BY ROLLUP(cls, nm) ) a ORDER BY a.cls, a.nm ;
) 버튼을 클릭하여 작성 하시면 됩니다.