SQL Condition for sum

Question

I have a sql statement with many inner join tables, as you can see below I have many conditional SUM statements , these sums are giving me wrong (very large) numbers as the inner join is repeating the same values in my source select pool. I was wondering id there is a way to limit these sum conditions lets say to EMPLIDs. The code is :

SELECT 
    A.EMPL_CTG, 
    B.DESCR AS PrName, 
    SUM(A.CURRENT_COMPRATE) AS SALARY_COST_BUDGET, 
    SUM(A.BUDGET_AMT) AS BUDGET_AMT, 
    SUM(A.BUDGET_AMT)*100/SUM(A.CURRENT_COMPRATE) AS MERIT_GOAL,
    SUM(C.FACTOR_XSALARY) AS X_Programp, 
    SUM(A.FACTOR_XSALARY) AS X_Program,
    COUNT(A.EMPLID) AS EMPL_CNT, 
    COUNT(D.EMPLID),
    SUM(CASE WHEN A.PROMOTION_SECTION = 'Y' THEN 1 ELSE 0 END) AS PRMCNT,
    SUM(CASE WHEN A.EXCEPT_IND = 'Y' THEN 1 ELSE 0 END) AS EXPCNT, 
    (SUM(CASE WHEN A.PROMOTION_SECTION = 'Y' THEN 1 ELSE 0 END)+SUM(CASE WHEN A.EXCEPT_IND = 'Y' THEN 1 ELSE 0 END))*100/(COUNT(A.EMPLID)) AS PEpercent 
FROM 
    EMP_DTL A INNER JOIN EMPL_CTG_L1 B ON A.EMPL_CTG = B.EMPL_CTG  
    INNER JOIN 
    ECM_PRYR_VW C ON A.EMPLID=C.EMPLID 
    INNER JOIN ECM_INELIG  D on D.EMPL_CTG=A.EMPL_CTG and D.YEAR=YEAR(getdate()) 
WHERE 
    A.YEAR=YEAR(getdate()) 
    AND B.EFF_STATUS='A' 
GROUP BY 
    A.EMPL_CTG, 
    B.DESCR 
ORDER BY B.DESCR

I already tried moving D.YEAR=YEAR(getdate()) to the where clause. Any help would be greatly appereciated

Answer 1

The probable reason of your very large numbers is probably due to the result of Cartesian product of joining A -> B, A -> C and A -> D where tables C and D appear to have multiple records. So, just example... if A has 10 records, and C has 10 for each of the A records, you now have 10 * 10 records... Finally, join that to D table with 10 records, you now have 10 * 10 * 10 for each "A", thus your bloated answers.

Now, how to resolve. I have taken your "C" and "D" tables and "Pre-Aggregated" those counts based on the join column basis. This way, they will each have only 1 record with the total already computed at that level, joined back to A table and you lose your Cartesian issue.

Now, for table B, it appears that is a lookup table only and would only be a single record result anyhow.

SELECT 
      A.EMPL_CTG, 
      B.DESCR AS PrName, 
      SUM(A.CURRENT_COMPRATE) AS SALARY_COST_BUDGET, 
      SUM(A.BUDGET_AMT) AS BUDGET_AMT, 
      SUM(A.BUDGET_AMT)*100/SUM(A.CURRENT_COMPRATE) AS MERIT_GOAL,
      PreAggC.X_Programp, 
      SUM(A.FACTOR_XSALARY) AS X_Program,
      COUNT(A.EMPLID) AS EMPL_CNT, 
      PreAggD.DCount,
      SUM(CASE WHEN A.PROMOTION_SECTION = 'Y' THEN 1 ELSE 0 END) AS PRMCNT,
      SUM(CASE WHEN A.EXCEPT_IND = 'Y' THEN 1 ELSE 0 END) AS EXPCNT, 
      ( SUM( CASE WHEN A.PROMOTION_SECTION = 'Y' THEN 1 ELSE 0 END
           + CASE WHEN A.EXCEPT_IND = 'Y' THEN 1 ELSE 0 END ) * 
           100 / COUNT(A.EMPLID) AS PEpercent 
   FROM 
      EMP_DTL A 
         INNER JOIN EMPL_CTG_L1 B 
            ON A.EMPL_CTG = B.EMPL_CTG  
           AND B.EFF_STATUS='A' 

         INNER JOIN ( select 
                            C.EMPLID,
                            SUM(C.FACTOR_XSALARY) AS X_Programp
                         from
                             ECM_PRYR_VW C
                         group by
                            C.EMPLID ) PreAggC
            ON A.EMPLID = PreAggC.EMPLID

         INNER JOIN ( select 
                            D.EMPLID,
                            COUNT(*) AS DCount
                         from
                             ECM_INELIG D 
                         where
                            D.Year = YEAR( getdate())
                         group by
                            D.EMPLID ) PreAggD
            ON A.EMPLID = PreAggD.EMPLID
   WHERE 
      A.YEAR=YEAR(getdate()) 
   GROUP BY 
      A.EMPL_CTG, 
      B.DESCR 
   ORDER BY 
      B.DESCR