select A.* , A.TOT - (A.RELIGN00 + A.RELIGN03 + A.RELIGN04 + A.RELIGN05 + A.RELIGN07) as RELIGN06 from ( SELECT DEPT.DEPTCD , DEPT.DEPTHNGNM , empl.* FROM (SELECT EMPL.UNITCD UNITCD , SUM(CASE WHEN empl.RELIGN = '02' AND (empl.BAPTDD IS NOT NULL AND empl.FAIRNMDD IS NULL) OR (empl.BAPTDD IS NULL AND empl.FAIRNMDD IS NOT NULL) OR (empl.BAPTDD IS NOT NULL AND empl.FAIRNMDD IS NOT NULL) AND empl.RTCONFMDD IS NULL THEN 1 ELSE 0 END) + SUM(CASE WHEN (empl.RELIGN) = '02' AND empl.BAPTDD IS NOT NULL AND empl.RTCONFMDD IS NOT NULL then 1 ELSE 0 END) RELIGN00 , SUM(CASE WHEN empl.RELIGN = '02' AND (empl.BAPTDD IS NOT NULL AND empl.FAIRNMDD IS NULL) OR (empl.BAPTDD IS NULL AND empl.FAIRNMDD IS NOT NULL) OR (empl.BAPTDD IS NOT NULL AND empl.FAIRNMDD IS NOT NULL) AND empl.RTCONFMDD IS NULL THEN 1 ELSE 0 END) RELIGN01 , SUM(CASE WHEN (empl.RELIGN) = '02' AND empl.BAPTDD IS NOT NULL AND empl.RTCONFMDD IS NOT NULL then 1 ELSE 0 END) RELIGN02 , SUM(CASE WHEN empl.RELIGN = '03' then 1 ELSE 0 END) RELIGN03 , SUM(CASE WHEN empl.RELIGN = '04' then 1 ELSE 0 END) RELIGN04 , SUM(CASE WHEN empl.RELIGN = '05' then 1 ELSE 0 END) RELIGN05 , SUM(CASE WHEN empl.RELIGN = '01' then 1 ELSE 0 END) RELIGN07 , SUM(CASE WHEN empl.RELIGN = '02' AND (empl.BAPTDD IS NOT NULL AND empl.FAIRNMDD IS NULL) OR (empl.BAPTDD IS NULL AND empl.FAIRNMDD IS NOT NULL) OR (empl.BAPTDD IS NOT NULL AND empl.FAIRNMDD IS NOT NULL) AND empl.RTCONFMDD IS NULL THEN 1 ELSE 0 END) +SUM(CASE WHEN (empl.RELIGN) = '02' AND empl.BAPTDD IS NOT NULL AND empl.RTCONFMDD IS NOT NULL then 1 ELSE 0 END) +SUM(CASE WHEN empl.RELIGN = '03' then 1 ELSE 0 END) +SUM(CASE WHEN empl.RELIGN = '04' then 1 ELSE 0 END) +SUM(CASE WHEN empl.RELIGN = '05' then 1 ELSE 0 END) +SUM(CASE WHEN empl.RELIGN = '01' then 1 ELSE 0 END) TOT FROM MIS.RPBMEMPL EMPL WHERE EMPL.INSTCD = '{?instcd}' AND EMPL.RETIREDD IS NULL GROUP BY EMPL.UNITCD ) EMPL LEFT OUTER JOIN COM.ZSDDDEPT DEPT ON DEPT.DEPTCD = EMPL.UNITCD WHERE DEPT.INSTCD = '{?instcd}' AND DEPT.VALIFROMDD <= TO_CHAR(SYSDATE,'YYYYMMDD') AND DEPT.VALITODD >= TO_CHAR(SYSDATE,'YYYYMMDD') ) A