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

Question

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

Answer 1

In my case I can't change the global variable of mysql. (I'm using prismadB with nextJS and rawQueryUnsafe function)

But there is another way.

select t1.field1, t1.field2, sum(t2.field3) from table1 t1 left join table2 on t1.field1=t2.field1

group by t1.field1 --> this fails

group by t1.field1, t1.field2 --> this works

you will need to add all the required fields to the group by

Tested on NextJs 14.0.4

With PrismadB 5.11.0

Mysql server 8.1.0

Answer 2

Open you WAMP panel and open MySQL configuration file. In it search for "sql_mode" if you find it set it to "" else if you don't find it add sql_mode="" to the file.

Restart the MySQL server and you are good to go...

happy coding.

Answer 3

In your my.ini, write this:

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

depend on your version. Or:

[mysqld]
sql_mode = ""

or simply remove this: ONLY_FULL_GROUP_BY