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:
- 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
- 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.