1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- 초간단 답변 WITH COUNTRY ( country , city, mon1 , mon2 ) AS ( SELECT '한국' , '서울' , 100 , 10 FROM DUAL UNION ALL SELECT '한국' , '부산' , 200 , 100 FROM DUAL UNION ALL SELECT '일본' , '도쿄' , 200 , 100 FROM DUAL UNION ALL SELECT '미국' , '뉴욕' , 50 , 100 FROM DUAL ) --부분및전체합 SELECT country , decode(1, grouping (country), '전체합계' , grouping (city), '부분합계' , city) city , sum (mon1) , sum (mon2) FROM COUNTRY GROUP BY ROLLUP ( country, city ) ; --전체합만 SELECT country , decode(1, grouping (country), '전체합계' , grouping (city), '부분합계' , city) city , sum (mon1) , sum (mon2) FROM COUNTRY GROUP BY GROUPING SETS ((country, city),()) ; |
1 2 3 4 5 6 7 8 9 10 11 | -- 위에 채용근님이 써주셨었는데 ㅡㅡ ... WITH COUNTRY ( country , area , mon1 , mon2 ) AS ( SELECT '한국' , 'SEOUL' , 100 , 10 FROM DUAL UNION ALL SELECT '한국' , 'BUSAN' , 100 , 10 FROM DUAL UNION ALL SELECT '미국' , 'NY' ,50 , 100 FROM DUAL UNION ALL SELECT '미국' , 'LA' ,50 , 100 FROM DUAL ) SELECT country , area , SUM (mon1) , SUM (mon2) FROM COUNTRY GROUP BY ROLLUP (( country ,area )) |