[RESOLVED] MySQL COUNT()

[The Little Guy]

When I run this In PHPmyADMIN
SELECT races.*,team_members.* FROM races,team_members WHERE COUNT(team_members.race_id) <= 9 AND races.race_id=team_members.race_id

I get this error:
Error

SQL query: Documentation

SELECT races. * , team_members. *
FROM races, team_members
WHERE COUNT( team_members.race_id ) <=9
AND races.race_id = team_members.race_id
LIMIT 0 , 30

MySQL said: Documentation
#1111 - Invalid use of group function

I'm trying to grab the total number of rows for each race WHERE race_id in the table team_members are the same, so I can print out a number from 1-10, Basically I want to display all the total number of members in a team, and if the team has 10 members already in it, don't even display that team.

[NightShift58]

This could work:SELECT races.*, team_members.*, count(team_members.race_id) as race_id_cnt
FROM races, team_members
WHERE race_id_cnt < 10
AND races.race_id=team_members.race_id
GROUP BY team_members.race_id

[The Little Guy]

#1054 - Unknown column 'race_id_cnt' in 'where clause'

[NightShift58]

Sorry, not thinking...SELECT races.*, team_members.*, count(team_members.race_id) as race_id_cnt
FROM races, team_members
WHERE races.race_id=team_members.race_id
GROUP BY team_members.race_id
HAVING race_id_cnt < 10

[The Little Guy]

Thank You

[NightShift58]

You're welcome!