mysqlgroup-bymysql-error-1055

SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

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 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
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