Having a problem with a basic join

Question

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 COUNT(). I'm new to joins so I'm sure its something trivial. Thanks in advance.

Answer 1

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

Answer 2

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