I need a SQL statement that returns the number rows in a table with a specific value.

Question

I'm sure this will equate down to the use of the COUNT statement but I'm not very good at SQL.

Here are my SQL Tables.

Teams Table:

TeamId     TeamName           RosterSpots 
----------------------------------------------- 
1          Miami Heat         12 
2          New York Knicks    10 

Players Table:

PlayerId   PlayerName         TeamId 
------------------------------------ 
1          Dwyane Wade        1 
2          Chris Bosh         1 
3          LeBron James       1 
4          Amar'e Stoudemire  2 

What is the SQL (Miscroft SQL Server 2008) that will return the number of Players on each team?

output

Team Name         PlayerCount
-----------------------------
Miami Heat        3
New York Knicks   1

I'd also like to return the RosterSpots and Team Id but really just the COUNT part above is what I'm puzzled with.

Answer 1

SELECT t.TeamID, RosterSpots, TeamName, COALESCE(COUNT(p.PlayerID),0) 
FROM Teams t 
LEFT JOIN Players p on t.TeamID = p.TeamID
GROUP BY TeamName,t.TeamID, RosterSpots

Should do the trick, but feel free to rename the count column to something more friendly.

Modify to a LEFT JOIN to allow for teams with no players

Answer 2

Use:

   SELECT t.teamid,
          t.teamname,
          COALESCE(COUNT(p.playerid), 0) AS playercount,
          t.rosterspots
     FROM TEAMS t
LEFT JOIN PLAYERS p ON p.teamid = t.teamid
 GROUP BY t.teamid, t.teamname, t.rosterspots