Search code examples
mysqlmysql-error-1064

mysql error #1064 when trying to limit results where count() not 0

I have query

SELECT helpc.cid, COUNT(help.cid) AS children_count FROM air_help_category AS helpc LEFT JOIN air_help AS help ON helpc.cid = help.cid GROUP BY cid WHERE hildren_count > 0

but I get

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE children_count > 0 LIMIT 0, 30' at line 1`

Checked the coresponding error code, but still could not fix this.

All I'm trying to achieve is to get those ids, who have at least one children.

Solution

  • First of all, WHERE clause should go before GROUP BY. Also, you can't use aggregate field values for WHERE - use HAVING instead, like this:

    SELECT helpc.cid, COUNT(help.cid) AS children_count 
FROM air_help_category AS helpc LEFT JOIN air_help AS help ON helpc.cid = help.cid 
GROUP BY cid 
HAVING COUNT(help.cid) > 0