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.


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, SUM(CASE WHEN T.eCode IS NULL THEN 0 ELSE 1) END AS cnt -- Sum up the number of eCodes per Employee, counting NULLs as 0
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 :) – Farid Siddiqui Feb 15 at 9:46
  • @FaridSiddiqui No problem! :) – J.D. Feb 15 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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