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

Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it — Taryn, Mar 19 '13 at 17:34
Oh sure. Its MySQL: assumed the SQL was so generic in this case that it wouldn't make a difference — Stumbler, Mar 19 '13 at 17:45
It might be straightforward sql, but you should always specify the rdbms you are using. :) — Taryn, Mar 19 '13 at 17:48

peterm · Accepted Answer · 2013-03-19T17:41:45.773

29

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

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

edited Mar 19 '13 at 17:41

answered Mar 19 '13 at 17:34

peterm

Can you achive same result using WHERE not JOINs? – f1ames May 10 '13 at 06:48
@f1ames What is the reason for that? – peterm May 10 '13 at 07:11
Curiosity, and I prefer using WHEREs over JOINs. Plus I have somehow complex query joining 3 tables and it's easier to modify it than creating whole new query with joins:) – f1ames May 10 '13 at 07:28

score 3 · Answer 2 · answered May 29 '18 at 16:39

3

It works for me

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

answered May 29 '18 at 16:39

SaliproPham

2 Answers2