I was working on some new reporting features for a client this evening when I encountered MySQL Error 1111 Invalid Use of Group Function. I was a bit befuddled by this as I was simply using a SUM total in my WHERE clause. Of course after a bit of googling I found that I cannot use an aggregate function such as SUM or COUNT as a WHERE clause.
Here is my original query.
SELECT SQL_CALC_FOUND_ROWS city.name AS city, state.abbrev AS state_abbrev, zip.zipcode, zip.city_id, COUNT(zip.zipcode) AS zip_count, SUM(assigned.is_primary) AS primary_count, SUM(assigned.is_backup) AS backup_count FROM tbl_zipcode zip INNER JOIN tbl_city city ON city.city_id = zip.city_id INNER JOIN tbl_state state ON state.state_id = city.state_id LEFT JOIN tbl_affiliate_assigned_location assigned ON assigned.zipcode = zip.zipcode WHERE zip_count > primary_count GROUP BY city.city_id ORDER BY COUNT(zip.zipcode) DESC, city.name ASC LIMIT 0,50
I fixed this using a HAVING clause on the GROUP BY.
SELECT SQL_CALC_FOUND_ROWS city.name AS city, state.abbrev AS state_abbrev, zip.zipcode, zip.city_id, COUNT(zip.zipcode) AS zip_count, SUM(assigned.is_primary) AS primary_count, SUM(assigned.is_backup) AS backup_count FROM tbl_zipcode zip INNER JOIN tbl_city city ON city.city_id = zip.city_id INNER JOIN tbl_state state ON state.state_id = city.state_id LEFT JOIN tbl_affiliate_assigned_location assigned ON assigned.zipcode = zip.zipcode GROUP BY city.city_id HAVING zip_count > primary_count ORDER BY COUNT(zip.zipcode) DESC, city.name ASC LIMIT 0,50
The HAVING clause seems to be one of those things I use so rarely that I forget about it just before I need it again. Hopefully since I wrote on a blog on it this time it sticks around in my memory bank a bit longer. Hope this helped someone. I also found out that you can have multple HAVING clauses using the following syntax.
HAVING zip_count > primary_count OR primary_count IS NULL