SELECT COUNT across one-to-many relationship

Question

For two tables

player

and

team

With a 1-∞ relationship (player to team): how would you count how many players belong to each team?

Failed attempt:

SELECT team.teamid, (SELECT COUNT(player.team) FROM player)
FROM team
LEFT JOIN player ON player.team = team.teamid

Answer 1

Try

SELECT t.teamid, COUNT(p.team) player_count
FROM team t LEFT JOIN
     player p ON p.team = t.teamid
GROUP BY t.teamid

SQLFiddle

It will give correct result event if some teams don't have players assigned. See sqlfiddle for that (team 3 doesn't have players).

Answer 2

It works for me

SELECT *, (select count(*) from receipt_note_stock b where 
b.receipt_note_id = a.id) from receipt_notes a