Aggregated query without GROUP BY
This query seems to work fine on my old machine. However, on my new machine with MySQL 5.7.14 and PHP 5.6.25, it seems to give an error:
Fatal error: Throw "PDOException" with message 'SQLSTATE [42000]: Syntax error or access violation: 1140 In an aggregate query without GROUP BY, SELECT expression # 1 contains the non-aggregated column "pixel_perfect.users.id"; this is incompatible with sql_mode = only_full_group_by 'in C: \ wamp64 \ www
This is what my request looks like:
$sql="SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email LIMIT 1";
$stmt=$db->prepare($sql);
$stmt->bindValue(':email', $email);
$stmt->execute();
Why am I getting this error now and what should I do to resolve it painlessly.
source to share
A has been changed in version-ish 5.7 , which will now be the default rejects the request, in which you agregiruete with the function ( sum
, avg
, max
etc.) in the proposal SELECT
and not to put non-aggregated fields in the proposal GROUP BY
. This behavior is an integral part of all other DBMSs and MySQL is finally jumping on board.
You have two options:
- You can change the default MySQL settings to the old behavior to allow smaller queries like this. Information can be found here
- You can correct your request
Option 2 looks something like this:
SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email GROUP BY id, password LIMIT 1
source to share
A bit late, but I just stumbled upon this error.
This command can be helpful to anyone who encounters the same error
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
More information on this can be found in Table Plus and other links linked above by JNevill .
Hope this helps someone else.
source to share