SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by
group-by, mysql, mysql-error-1055
Solution
This
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
will be simply solved by changing the sql mode in MySQL by this command.
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
This too works for me. I used this, because in my project there are many queries like this so I just changed the sql mode to only_full_group_by.
OR simply include all columns in the GROUP BY clause that was specified by the SELECT statement. The sql_mode can be left enabled.
Thank You. :-)
Updated:14 Jul 2023
Changing SQL mode is a solution, but still, the best practice for Structured Query Language will be avoid selecting all (SELECT * ...) columns, instead use aggregator functions on the grouping columns as mentioned by @Tim Biegeleisen below answers https://stackoverflow.com/a/41887524/3602846
Problem
I'm using MySQL 5.7.13 on my windows PC with WAMP Server. My problem is while executing this query ``` SELECT * FROM `tbl_customer_pod_uploads` WHERE `load_id` = '78' AND `status` = 'Active' GROUP BY `proof_type` ``` I'm getting always error like this. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Can you please tell me the best solution? My result should be like below: ``` +----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+ | id | user_id | load_id | bill_id | latitude | langitude | proof_type | document_type | file_name | is_private | status | createdon | updatedon | +----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+ | 1 | 1 | 78 | 1 | 21.1212 | 21.5454 | 1 | 1 | id_Card.docx | 0 | Active | 2017-01-27 11:30:11 | 2017-01-27 11:30:14 | +----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+ ```