SELECT c.up_org_cd , (SELECT cm_cd_nm FROM cm_cd_info x WHERE 1=1 AND x.cm_cd = c.up_org_cd AND cm_cls_cd = 'P0029' ) AS up_org_nm , NVL(c.org_nm , '합계') org_nm , NVL(c.aff_org_nm, '소계') aff_org_nm , SUM(a.cnt) AS cnt , ROUND(RATIO_TO_REPORT(SUM(a.cnt)) OVER(PARTITION BY GROUPING_ID(c.org_nm, c.aff_org_nm)) * 100, 1) rat FROM (SELECT resorce_dist_no , COUNT(*) AS cnt FROM pt_log_info WHERE 1=1 AND resorce_dist_no IS NOT NULL GROUP BY resorce_dist_no ) a , rs_bio_resorce_cm_info b , cm_org_info c WHERE 1=1 AND a.resorce_dist_no = b.resorce_dist_no AND b.aff_org_cd = c.aff_org_cd AND c.up_org_cd = 'A' GROUP BY ROLLUP ((c.up_org_cd, c.org_nm), c.aff_org_nm) ORDER BY c.up_org_cd, c.org_nm, c.aff_org_nm ;
SELECT c.up_org_cd , x.cm_cd_nm AS up_org_nm , NVL(c.org_nm , '합계') org_nm , DECODE(GROUPING_ID(c.org_nm, c.aff_org_nm), 1, '소계', c.aff_org_nm) aff_org_nm , COUNT(*) AS cnt , ROUND(RATIO_TO_REPORT(COUNT(*)) OVER(PARTITION BY GROUPING_ID(c.org_nm, c.aff_org_nm)) * 100, 1) rat FROM pt_log_info a , rs_bio_resorce_cm_info b , cm_org_info c , cm_cd_info x WHERE 1=1 AND a.resorce_dist_no IS NOT NULL AND a.resorce_dist_no = b.resorce_dist_no AND b.aff_org_cd = c.aff_org_cd AND c.up_org_cd = 'A' AND x.cm_cd = c.up_org_cd AND x.cm_cls_cd = 'P0029' GROUP BY c.up_org_cd, x.cm_cd_nm, ROLLUP (c.org_nm, c.aff_org_nm) ORDER BY c.org_nm, c.aff_org_nm ;
그룹바이를 2 단계로 나누어 하세요.
- 1 단계 : 집계 및 비율 구하고
- 2 단계 : 롤업 합산 처리
SELECT up_org_cd , up_org_nm , NVL(org_nm, '합계') org_nm , DECODE(GROUPING_ID(org_nm, aff_org_nm), 1, '소계', aff_org_nm) aff_org_nm , SUM(cnt) cnt , DECODE(GROUPING_ID(org_nm, aff_org_nm), 3, 100, SUM(rat)) rat FROM (SELECT c.up_org_cd , x.cm_cd_nm AS up_org_nm , c.org_nm , c.aff_org_nm , COUNT(*) AS cnt , ROUND(RATIO_TO_REPORT(COUNT(*)) OVER() * 100, 1) rat FROM pt_log_info a , rs_bio_resorce_cm_info b , cm_org_info c , cm_cd_info x WHERE 1=1 AND a.resorce_dist_no IS NOT NULL AND a.resorce_dist_no = b.resorce_dist_no AND b.aff_org_cd = c.aff_org_cd AND c.up_org_cd = 'A' AND x.cm_cd = c.up_org_cd AND x.cm_cls_cd = 'P0029' GROUP BY c.up_org_cd, x.cm_cd_nm, c.org_nm, c.aff_org_nm ) a GROUP BY up_org_cd, up_org_nm, ROLLUP(org_nm, aff_org_nm) ORDER BY a.org_nm, a.aff_org_nm ;