안녕하세요...
다음 표를 순번 1~10, 11~13을 나눠서 부분합을 구하고 싶습니다.
도움 요청좀 드릴께요 ㅠㅠ
순번 | 수량 | 구분자 |
1 | 976 | A |
2 | 976 | A |
3 | 948 | A |
4 | 837 | A |
5 | 1379 | A |
6 | 1327 | A |
7 | 0 | A |
8 | 1970 | A |
9 | 0 | A |
10 | 0 | A |
11 | 0 | B |
12 | 1226 | B |
13 | 1325 | B |
다음과 같이 바꾸고 싶습니다.
순번 | 수량 | 구분자 |
1 | 976 | A |
2 | 976 | A |
3 | 948 | A |
4 | 837 | A |
5 | 1379 | A |
6 | 1327 | A |
7 | 0 | A |
8 | 1970 | A |
9 | 0 | A |
10 | 0 | A |
소계 | 8413 | |
11 | 0 | B |
12 | 1226 | B |
13 | 1325 | B |
소계 | 2551 | |
합계 | 10964 |
1~10, 11~13 으로 나누는 것 맞나요?
순번으로 나누는게 아니라 구분으로 나누는 것 아닌가요? (A, B)
WITH t AS ( SELECT 1 seq, 976 cnt, 'A' gb FROM dual UNION ALL SELECT 2, 976, 'A' FROM dual UNION ALL SELECT 3, 948, 'A' FROM dual UNION ALL SELECT 4, 837, 'A' FROM dual UNION ALL SELECT 5, 1379, 'A' FROM dual UNION ALL SELECT 6, 1327, 'A' FROM dual UNION ALL SELECT 7, 0, 'A' FROM dual UNION ALL SELECT 8, 1970, 'A' FROM dual UNION ALL SELECT 9, 0, 'A' FROM dual UNION ALL SELECT 10, 0, 'A' FROM dual UNION ALL SELECT 11, 0, 'B' FROM dual UNION ALL SELECT 12, 1226, 'B' FROM dual UNION ALL SELECT 13, 1325, 'B' FROM dual ) SELECT DECODE(GROUPING_ID(gb, seq), 3, '합계', 1, '소계', seq) seq , SUM(cnt) cnt , gb FROM t GROUP BY ROLLUP(gb, seq) ;
WITH T ( NO , QTY , TP ) AS ( SELECT 1 , 976 , 'A' FROM DUAL UNION ALL SELECT 2 , 976 , 'A' FROM DUAL UNION ALL SELECT 3 , 948 , 'A' FROM DUAL UNION ALL SELECT 4 , 837 , 'A' FROM DUAL UNION ALL SELECT 5 , 1379 , 'A' FROM DUAL UNION ALL SELECT 6 , 1327 , 'A' FROM DUAL UNION ALL SELECT 7 , 0 , 'A' FROM DUAL UNION ALL SELECT 8 , 1970 , 'A' FROM DUAL UNION ALL SELECT 9 , 0 , 'A' FROM DUAL UNION ALL SELECT 10 , 0 , 'A' FROM DUAL UNION ALL SELECT 11 , 0 , 'B' FROM DUAL UNION ALL SELECT 12 , 1226 , 'B' FROM DUAL UNION ALL SELECT 13 , 1325 , 'B' FROM DUAL ) SELECT CASE WHEN GROUPING(TP) = 1 THEN '합계' WHEN GROUPING(NO) = 1 THEN '소계' ELSE TO_CHAR(NO) END NO , SUM(QTY) QTY , TP FROM T GROUP BY ROLLUP (TP,(NO))