Q:

List freqüents with nuls values

I have a DB in Mysql where I want to count how many type of Players has a team. My problem is that I want to show all the type of players, even if the team does not have that kind of player.
Let me show you the data Tables:
INSERT INTO typePlayer (id_typeplayer,type_player,id_race) VALUES
(57,"zombi",15),
(58,"necrofago",15),
(59,"tumulario",15),
(60,"Hobre Lobo",15),
(61,"Golem de Carne",15);

INSERT INTO teams (id_team, id_race) VALUES 
(7,15);

INSERT INTO Players (id_player, id_typeplayer, id_team) VALUES
(1,60,7),
(2,60,7),
(3,58,7),
(4,58,7),
(5,59,7),
(6,59,7),
(7,57,7),
(8,57,7),
(9,57,7),
(10,57,7),
(11,57,7);

I'd love to have a query that gives me this:
57,zombi,5
58,necrofago,2
59,tumulario,2
60,Hombre Lobo,2
61,Golem de Carne,0

My problem is that I cannot show the last row (where count is null, because the team has no player of that type)
I've tried this, with bad result:
SELECT tP.id_typeplayer,
       count(p.id_typeplayer) cant,
       p.id_team
FROM players p
JOIN typePlayer tP ON(tP.id_typeplayer=p.id_typeplayer)
WHERE (p.status='En activo'
       OR p.status='Lesionado')
  AND p.independiente='No'
  AND p.zombificado='No'
  AND p.id_team=7
  AND tP.id_race=15
GROUP BY tP.id_typeplayer HAVING cant>=0

I'll thank any help

A:

Try this SQL, what you need is to include all typePlayer tuples even if there are no players of that type. That would be a left join if typePlayer table is on the left hand side or a right join if typePlayer table is on the right hand side.
I actually went to the link you provided in sqlfiddle. Your typePlayer table is on the right, so I changed it to a right join. The resultant SQL is as follows
SELECT tP.id_typeplayer,tp.type_player,
   sum(if(p.id_team is null, 0,1)) cant,
   p.id_team
FROM players p
RIGHT JOIN typePlayer tP ON(tP.id_typeplayer=p.id_typeplayer)
GROUP BY tP.id_typeplayer

The other thing you mentioned in your comment is to have only id_team=7 which I think you just need to filter out in a where clause like this.
SELECT tP.id_typeplayer,tp.type_player,
   sum(if(p.id_team is null, 0,1)) cant,
   p.id_team
FROM players p
RIGHT JOIN typePlayer tP ON(tP.id_typeplayer=p.id_typeplayer)
WHERE p.id_team=7 or p.id_team is null    
GROUP BY tP.id_typeplayer

