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.