How to get count 0 using group by if record not exist in the same table?

Question

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

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.

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
   CROSS JOIN Test AS EC
)

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