Aggregated query without GROUP BY

A change was made in version 5.7-ish where it will now, by default, reject queries in which you aggregate using a function (sum, avg, max, etc.) in the SELECT clause and fail to put the non-aggregated fields in the GROUP BY clause. This behavior is part and parcel to every other RDBMS and MySQL is finally jumping on board.

You have two options:

  1. You can change the MySQL settings to default to the old behavior to allow not-so-great queries like this. Information can be found here
  2. You can fix your query

Option 2 would look something like:

SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email GROUP BY id, password LIMIT 1

Its a little late but I just ran into this error.

This command might be useful for anyone else who runs into the same error

     mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

More information about this can be found at Table Plus and other links quoted above by JNevill.

Hope it helps someone else.