SELECT dept.depthngnm AS depthngnm ,SUM(CASE WHEN aa.CLSCD = 'A0003'then 1 ELSE 0 END) AS principle --이념교육 ,SUM(CASE WHEN aa.CLSCD = 'A0011'then 1 ELSE 0 END) AS daerimcour --특강(대림) ,SUM(CASE WHEN aa.CLSCD = 'A0012'then 1 ELSE 0 END) AS sassooncour --특강(사순) ,SUM(CASE WHEN aa.CLSCD = 'A0013'then 1 ELSE 0 END) AS biblecour --특강(성서) ,SUM(CASE WHEN aa.CLSCD = 'A0005'then 1 ELSE 0 END) AS teachertour --교직원피정 ,SUM(CASE WHEN aa.CLSCD = 'A0006'then 1 ELSE 0 END) AS bibleedu --성서교육 ,SUM(CASE WHEN aa.CLSCD = 'A0007'then 1 ELSE 0 END) AS faithlecture --신앙강좌 ,SUM(CASE WHEN aa.CLSCD = 'A0008'then 1 ELSE 0 END) AS believerreedu --신자재교육 ,SUM(CASE WHEN aa.CLSCD = 'A0010'then 1 ELSE 0 END) AS otheredu --기타교육 ,(SUM(CASE WHEN aa.CLSCD = 'A0003'then 1 ELSE 0 END) + SUM(CASE WHEN aa.CLSCD = 'A0011'then 1 ELSE 0 END) + SUM(CASE WHEN aa.CLSCD = 'A0012'then 1 ELSE 0 END) + SUM(CASE WHEN aa.CLSCD = 'A0013'then 1 ELSE 0 END) + SUM(CASE WHEN aa.CLSCD = 'A0005'then 1 ELSE 0 END) + SUM(CASE WHEN aa.CLSCD = 'A0006'then 1 ELSE 0 END) + SUM(CASE WHEN aa.CLSCD = 'A0007'then 1 ELSE 0 END) + SUM(CASE WHEN aa.CLSCD = 'A0008'then 1 ELSE 0 END) + SUM(CASE WHEN aa.CLSCD = 'A0010'then 1 ELSE 0 END)) AS depttot FROM (SELECT efdm.instcd AS instcd , attd.attntrid AS attntrid , efdm.clscd AS clscd , efdm.miletm AS miletm , efdm.fromdd AS fromdd , efdm.todd AS todd , efdm.plcenm AS plcenm , efdm.lactrnm AS lactrnm , efdm.chrgconslrid AS chrgconslrid FROM ast.ayahefdm efdm , ast.ayadattd attd WHERE attd.instcd = efdm.instcd AND attd.clscd = efdm.clscd AND attd.seqno = efdm.seqno AND attd.DELDD = '00000000' AND efdm.clsflag = 'A' AND efdm.fromdd BETWEEN '{?fromdd}'||'01' AND '{?todd}'||'31' AND efdm.deldd = '00000000' AND efdm.instcd = '{?instcd}' ) aa INNER JOIN ( SELECT CLAS.CLSCD AS CLSCD , CLAS.CLSNM AS CLSNM , CLAS.CLSSUPCD AS CLSSUPCD , CASE CLAS.CLSLEVL WHEN '3' THEN CAST(CLSCD AS VARCHAR(300)) ELSE CLAS.CLSSUPCD||CAST(CLSCD AS VARCHAR(300)) END AS PATHCD FROM AST.AYAMCLAS CLAS START WITH CLAS.CLSFLAG = 'A' AND CLAS.CLSLEVL = '3' AND CLAS.DELDD = '00000000' AND CLAS.SCRNDISPYN = 'Y' AND CLAS.INSTCD = '{?instcd}' CONNECT BY NOCYCLE PRIOR CLSCD=CLSSUPCD ORDER BY CLSCD ) TMP ON ( TMP.CLSCD=aa.clscd ) LEFT OUTER JOIN mis.RPBVEMPL empl ON (empl.emplno = aa.attntrid ) LEFT OUTER JOIN com.zsdddept dept ON (dept.instcd = aa.instcd AND dept.deptcd = empl.unitcd AND (CASE WHEN empl.retiredd IS NULL then TO_CHAR(SYSDATE,'YYYYMMDD') else empl.retiredd END) BETWEEN dept.valifromdd AND dept.valitodd ) WHERE aa.instcd = '{?instcd}' GROUP BY dept.depthngnm