MySQL Invalid use of group function on shared host

Question

I need to know the team with highest number of matches in a certain city.

SELECT COUNT(am_matches.team_id) AS matches_count,
am_matches.team_id AS team_id
FROM am_matches
LEFT JOIN am_team ON (am_matches.team_id = am_teams.id)
WHERE am_matches.status = '3' AND am_teams.city_id = '$city_id'
GROUP BY am_matches.team_id
ORDER BY COUNT(am_matches.team_id) DESC LIMIT 1

This works on a local server (wamp), but on a shared host throws "Invalid use of group function". Is there another way of getting this? I need to make a lot of similar querys so I would like to know a more readable approach of getting similar values. Thanks.

Answer 1

You can order by "1" as the comment said, or you can use the column alias "matches_count". It's preferable and expected to use the alias, not the numerical index, for clarity and readability sake. You would only re-invoke grouping functions in a HAVING clause, never in ORDER. By the time ORDER sees your results, the data is all assembled and just gets sorted.

ORDER BY matches_count DESC LIMIT 1