Having a problem with a basic join

I have a table called tblSport which contains the columns pkSportID and fldName.

I have a table called tblRespondentSport which contains the columns fkRespondentID and fkSportID.

I want to return the sport ID, the name of the sport, and the number of respondents.

Here's my query:

SELECT s.pkSportID AS id, s.fldSport AS sport, r.COUNT(*) AS count FROM tblSport AS s LEFT JOIN tblRespondentSport AS r ON s.pkSportID = r.fkSportID

I'm getting a MySQL 1064 error and it says its near the * in COUN开发者_如何学CT(). I'm new to joins so I'm sure its something trivial. Thanks in advance.

You are missing Group BY

SELECT s.pkSportID AS id, s.fldSport AS sport, COUNT(*) AS count 
FROM tblSport AS s LEFT JOIN tblRespondentSport AS r 
ON s.pkSportID = r.fkSportID
GROUP BY s.pkSportID, s.fldSport

I think you need to change r.COUNT(*) to COUNT(r.*). or even just COUNT(*)