I need some expert advice please.

I have a table with Employees and eCodes in SQL server. I want to get the count of eCodes for each employee but in some cases an employee doesn't have a certain eCode.

Where the eCode is missing for an employee I want to return zero.

SELECT Employee, eCode, Count(*) cnt FROM Test
Group By Employee, eCode
Order by Employee, eCode

The above SQL gives me following output

enter image description here

David is missing eCode X and Y, which Gary and Tony has. How can I return zero for its count. Similarly Gary and Tony is missing F and G and I want to get zero for those two.

I can use some help here.


1 Answer 1


This should accomplish what you're looking for but this assumes all of your eCodes exist at least for one record in your Test table. Otherwise you can replace the Test table instance on the right side of the CROSS JOIN in the CTE below with your source table of eCodes:

WITH CTE_EmployeeECodes AS -- Gets a distinct list of all Employee and eCode combinations
   SELECT DISTINCT E.Employee, EC.eCode
   FROM Test AS E

SELECT EEC.Employee, EEC.eCode, COUNT(T.eCode) AS cnt
FROM CTE_EmployeeECodes AS EEC
LEFT JOIN Test AS T -- Left join allows us to keep all records from our source Employee & eCode combinations CTE, but T.eCode will be NULL for the eCodes an employee has 0 instances of
    ON EEC.Employee = T.Employee
    AND EEC.eCode = T.eCode
GROUP BY EEC.Employee, EEC.eCode
ORDER BY EEC.Employee, EEC.eCode
  • Thanks J.D. I need to brush up my CROSS JOIN :) Feb 15, 2021 at 9:46
  • @FaridSiddiqui No problem! :)
    – J.D.
    Feb 15, 2021 at 13:30
  • Nice response! If I'm not mistaken, there seems to be a small syntax error in the CASE statement. Doesn't the END belong inside the SUM() function? Like: [...] SUM(CASE WHEN T.eCode IS NULL THEN 0 ELSE 1 END) AS [...]?
    – fujiu
    Mar 1, 2022 at 13:43
  • @fujiu Yes, good catch! I just fixed it. Sorry I usually type up my answers on my phone and occasionally miss things.
    – J.D.
    Mar 1, 2022 at 18:44
  • 1
    SUM(CASE WHEN T.eCode IS NULL THEN 0 ELSE 1 END) can be simplified to COUNT(T.eCode) Mar 1, 2022 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.