sql server - SQL not doing the join correctly

Question

I have a SQL statement with some JOIN condition it is working fine for all of them but not the last one the code is below:

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

The COUNT(D.EMPLID) is returning the same value as COUNT(A.EMPLID) but I need the count of EMPLIDs for Table D in the join condition, any help?

Answer 1

COUNT() (and also the other GROUP BY aggregate functions) doesn't process only the rows from one of the tables.

They work on all the rows produced by the JOIN. If the JOIN without GROUP BY produces 42 rows then COUNT(*) and COUNT(1) returns 42 while COUNT(A.EMPLID) and COUNT(D.EMPLID) return the number of not-NULL values in those columns.

In order to get the number of rows extracted from one of the tables the you should use COUNT(DISTINCT). It ignores the NULL values and also the duplicates produced by the JOIN.

Change COUNT(D.EMPLID) to COUNT(DISTINCT D.EMPLID).