SELECT TotA.rcpn_path_nm, TotA.rcpn_path_cd, TotA.rcpn_type_lcls_nm, TotA.rcpn_type_lcls_cd, NVL(TotB.CNT, 0) as cnt, NVL(sum(TotB.cnt) over(partition by TotA.rcpn_path_nm), 0) as sum_cnt, NVL(round(TotB.cnt/ sum(TotB.cnt) over(partition by TotA.rcpn_path_nm), 3), 0) as perc FROM ( Select Table_A.rcpn_path_nm, Table_A.rcpn_path_cd, Table_A.A_prnt_seq, Table_B.rcpn_type_lcls_nm, Table_B.rcpn_type_lcls_cd, Table_B.B_prnt_seq From (SELECT cdnm as rcpn_path_nm, cdid as rcpn_path_cd, dispseq AS A_prnt_seq FROM com.zbcmcode WHERE cdgrupid = 'C0001') Table_A, (SELECT cdnm as rcpn_type_lcls_nm, cdid as rcpn_type_lcls_cd, dispseq AS B_prnt_seq FROM com.zbcmcode WHERE cdgrupid = 'C0003') Table_B ) TotA LEFT JOIN ( select acpttypecd as rcpn_path_cd, acptkindlrgcd as rcpn_type_lcls_cd, count(*) AS CNT from crm.cuvhacpi where acptdd >= '{?srchacptdd1}' and acptdd <= '{?srchacptdd2}' and cnclyn = 'N' and instcd = '{?instcd}' group by acpttypecd, acptkindlrgcd ) TotB ON TotA.rcpn_path_cd = TotB.rcpn_path_cd AND TotA.rcpn_type_lcls_cd = TotB.rcpn_type_lcls_cd order by TotA.A_prnt_seq, TotA.B_prnt_seq