SELECT COUNT from two tables is not working

I have two tables – first table is “projects”:

p_id	p_name
1	test1
2	test2
3	test2
4	test3

p_name is not unique

The second table is “employee”:

e_id	jan	feb	mar	apr	may
1	2	2	3	3	4
2	1	1	3	3	2
3	1	3	2	3	3
4	4	3	2	3	4

I’d like to count how many p_name in employee depending on name

What I do:

select 
    p.id, p.name, 
    count(e1.jan) as e1, 
    count(e2.feb) as e2, 
    count(e3.mar) as e3  
from 
    projects p 
left join 
    employee e1 on (e1.jan = p.id) 
left join 
    employee e2 on (e2.feb = p.id) 
left join 
    employee e3 on (e3.mar = p.id) 
where 
    p.name = 'test2' 
group by 
    p.name;

But it doesn’t work. It doesn’t return the expected numbers.

Answer

I completely agree with the comment of @sTTu – the chosen design is not scalable and will run into problems as soon as a “new year” starts.

Purely out of curiousity I tried to come up with a solution nonetheless which would be the following:

(SQL-Server – but should also work in MySql)

SELECT name, SUM(jan) Jan, SUM(feb) feb, SUM(mar) mar FROM (
  SELECT id, name, 
    (SELECT count(*) FROM employee WHERE jan=id) jan,
    (SELECT count(*) FROM employee WHERE feb=id) feb,
    (SELECT count(*) FROM employee WHERE mar=id) mar
  from projects ) tbl
group by name;

Here is a working snippet: https://rextester.com/TBWZA82894

Result:

name	Jan	feb	mar
test1	2	1	0
test2	1	3	4
test3	1	0	0

Or, in an even simpler way you can do it like this:

SELECT name,
  COUNT(CASE WHEN jan=id THEN 1 END) jan,
  COUNT(CASE WHEN feb=id THEN 1 END) feb,
  COUNT(CASE WHEN mar=id THEN 1 END) mar
FROM employee 
INNER JOIN projects ON id>0
GROUP BY name

see the demo here: https://rextester.com/DEUUE59264