저번에 마농이 알려 주신 sql의 하단에 합계값의 비율을 넣고 싶어서
아래와 같이 수정 했습니다만..
뭔가 다른 방법이 있을 것도 같아서
마농님이 한번 봐 주셨으면 합니다.
감사합니다.
SELECT *
FROM (SELECT NVL(a.code, '합계') code
, NVL(SUBSTR(b.sbscrb_de, 1, 4), '합계') yyyy
, COUNT(b.mber_id) cnt
FROM egov.comtccmmndetailcode a
, tbo_cm_mberinfo_list_bak b
WHERE a.code_id = 'MEM_GBN'
AND b.mber_confm_code = 'Y'
AND a.code
= CASE WHEN b.mber_se_code IN ('7', '9', '10', '12') THEN '1'
WHEN b.mber_se_code IN ('3', '4', '11' ) THEN '6'
ELSE b.mber_se_code END
AND b.sbscrb_de IS NOT NULL
GROUP BY CUBE(a.code, SUBSTR(b.sbscrb_de, 1, 4))
)
PIVOT (SUM(cnt) FOR code IN ( '1' 구분1
, '2' 구분2
, '5' 구분5
, '6' 구분6
, '8' 구분8
, '13' 구분13
, '합계' 합계
) )
union all
select
'합계 비율(%)' mberSeCode
,ROUND(sum(구분1)/SUM(구분1+구분2+구분5+구분6+구분8+구분13)*100,2) 구분1
,ROUND(sum(구분2)/SUM(구분1+구분2+구분5+구분6+구분8+구분13)*100,2) 구분2
,ROUND(sum(구분3)/SUM(구분1+구분2+구분5+구분6+구분8+구분13)*100,2) 구분3
,ROUND(sum(구분5)/SUM(구분1+구분2+구분5+구분6+구분8+구분13)*100,2) 구분5
,ROUND(sum(구분6)/SUM(구분1+구분2+구분5+구분6+구분8+구분13)*100,2) 구분6
,ROUND(sum(구분8)/SUM(구분1+구분2+구분5+구분6+구분8+구분13)*100,2) 구분8
,ROUND(sum(구분13)/SUM(구분1+구분2+구분5+구분6+구분8+구분13)*100,2) 구분13
, 100 합계
FROM (SELECT
aa.code as mberSeCode
,count(bb.mber_id) as bb
FROM egov.comtccmmndetailcode aa
-- , tbo_cm_mberinfo_list_bak bb
, tbo_cm_mberinfo_list bb
WHERE aa.code_id = 'MEM_GBN'
AND bb.mber_confm_code = 'Y'
AND aa.code
= CASE WHEN bb.mber_se_code IN ('7', '9', '10', '12') THEN '1'
WHEN bb.mber_se_code IN ('3', '4', '11' ) THEN '6'
ELSE bb.mber_se_code END
AND bb.sbscrb_de IS NOT NULL
AND SUBSTR(bb.SBSCRB_DE,1,6) >= #{searchSYM}
AND SUBSTR(bb.SBSCRB_DE,1,6) <= #{searchEYM}
group by aa.code
)
PIVOT (sum(bb) FOR mberSeCode IN ( '1' 구분1
, '2' 구분2
, '5' 구분5
, '6' 구분6
, '8' 구분7
, '합계' 구분13
) )
ORDER BY 1
|