Xrhrft

This query creates a table but the resultant table has dups on ACT_CAND , i can get the correct count on ACT_CAND but only after running a subquery on the table. I am looking for way that i can get my count by just executing this code
select sum(ACT_CAND) from table..

All the tables involved have a 1:M except for motives_d.app_event_wid = app_fact.appl_csw_motives_wid , the motives_d has a many to many relationship
M:M with the fact table

SELECT 

    "CAN_NUM",

    "J_GROUP",

    "PI_COMP_DT",

    "LAST_PI_COMP_DT",

    "DIFF_DATE",

    "J_FAM_N",

    "R_NUM",

    "LST_SM_DT",

    "INTEGRATION_ID",

    "WRKFLW_NM",

    "TRK_STS_NAM",

    "TRK_STEP_NAM",

    "MTVE_NAME",

    "ACT_CAND" 

FROM 

    (SELECT 

       APP_FACT.PI_CANDIDATE_NUM AS CAN_NUM, 

       JOB_INFO_D.J_GROUP AS J_GROUP, 

       APP_FACT.PI_COMP_DT AS PI_COMP_DT, 

       NULL AS LAST_PI_COMP_DT, 

       NULL AS DIFF_DATE, 

       JOB_INFO_D.J_FAM_N AS J_FAM_N, 

       REQN_D.R_NUM AS R_NUM, 

       APP_FACT.LAST_SBM_DT AS LST_SM_DT, 

       APP_FACT.INTEGRATION_ID AS INTEGRATION_ID, 

       APP_FACT.WRKFLW_NM AS WRKFLW_NM, 

       APP_FACT.TRK_STS_NAM AS TRK_STS_NAM, 

       APP_FACT.TRK_STEP_NAM AS TRK_STEP_NAM, 

       MOTIVES_D.MTVE_NAME AS MTVE_NAME, 

       CASE WHEN 

         APP_FACT.INITIAL_APP_MEDIUM_ROW_WID IN (SELECT 

                                                   ROW_WID 

                                                 FROM 

                                                   WC_APPLICATION_MEDIUM_D 

                                                 WHERE 

                                                   CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME')) 

                                                 OR

                                                   APP_FACT.APP_MEDIUM_ROW_WID IN (SELECT 

                                                                                     ROW_WID 

                                                                                   FROM 

                                                                                     WC_APPLICATION_MEDIUM_D 

                                                                                   WHERE 

                                                                                     CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME')) 

       THEN 0 ELSE null END ACT_CAND 

     FROM 

       ABC_EVENT_F APP_FACT, 

       DEF_ROD_D REQN_D, 

       GHI_INFO_D JOB_INFO_D, 

       JKL_APPLE_MTV_D MOTIVES_D 

     WHERE 

       APP_FACT.REQUISITION_ROW_WID = REQN_D.ROW_WID 

       AND APP_FACT.JOB_INFO_ROW_WID = JOB_INFO_D.ROW_WID 

       AND MOTIVES_D.APP_EVENT_WID = APP_FACT.APPL_CSW_MOTIVES_WID 

     GROUP BY 

       APP_FACT.PI_CANDIDATE_NUM A, 

       JOB_INFO_D.J_GROUP , 

       APP_FACT.PI_COMP_DT , 

       JOB_INFO_D.J_FAM_N , 

       REQN_D.R_NUM A, 

       APP_FACT.LAST_SBM_DT , 

       APP_FACT.INTEGRATION_ID , 

       APP_FACT.WRKFLW_NM, 

       APP_FACT.TRK_STS_NAM , 

       APP_FACT.TRK_STEP_, 

       MOTIVES_D.MTVE_NAME , 

       CASE WHEN 

         APP_FACT.INITIAL_APP_MEDIUM_ROW_WID IN (SELECT 

                                                   ROW_WID 

                                                 FROM 

                                                   WC_APPLICATION_MEDIUM_D 

                                                 WHERE 

                                                   CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME')) 

                                                   OR 

                                                   APP_FACT.APP_MEDIUM_ROW_WID IN (SELECT 

                                                                                     ROW_WID 

                                                                                   FROM 

                                                                                     WC_APPLICATION_MEDIUM_D 

                                                                                   WHERE 

                                                                                     CODE IN ('IMPORT','MATCHED_TO_JOB','RESUME')) 

       THEN 0 ELSE null 

     ORDER BY 

       APP_FACT.PI_CANDIDATE_NUM,

       APP_FACT.INTEGRATION_ID,

       JOB_INFO_D.J_GROUP ASC ) IR1 

WHERE 

    IR1.ACT_CAND = 0;