mysql: ERROR 1064 (42000): You have an error in your SQL syntax near 'GROUP BY ...'

This is a wiki page. Be bold and improve it!

If you have any questions about the content on this page, don't hesitate to open a new ticket and we'll do our best to assist you.

GROUP BY ... HAVING

The query:

mysql> SELECT id, COUNT(id) AS count FROM my_table HAVING count > 1 GROUP BY id;

would result in the error:
ERROR 1064 (42000): 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 'GROUP BY id' at line 1

HAVING must come after GROUP BY. Simply invert the two clauses:

mysql> SELECT id, COUNT(id) AS count FROM my_table GROUP BY id  HAVING count > 1;

GROUP BY ... ORDER BY

As above, GROUP BY must come before ORDER BY. Make sure the two clauses are not inverted.