SQL_MODE=only_full_group_by: The SELECT list is not in the GROUP BY clause and contains non-aggregated columns....Incompatible

Question

I'm using MySQL 5.7.13 and WAMP server on a Windows PC

My problem is when executing this query

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `proof_type`

I always encounter this error

Expression #1 of the SELECT list is not in the GROUP BY clause and contains the non-aggregated column "returntr_prod.tbl_customer_pod_uploads.id", which is not functionally dependent on the columns in the GROUP BY clause; this is consistent with sql_mode= only_full_group_by incompatible

Can you tell me the best solution?

I need this result

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

There is a system variable ONLY_FULL_GROUP_BY in the MySql engine.

Starting from Mysql version 5.7.5: ONLY_FULL_GROUP_BYSQL mode is enabled by default

Prior to version 5.7.5: ONLY_FULL_GROUP_BY is not enabled by default.

If ONLY_FULL_GROUP_BY SQL mode is enabled (enabled by default starting with version 5.7.5), MySQL will reject select lists, HAVING conditions, or ORDER BY lists Refers to non-aggregate columns that are neither named in a GROUP BY clause nor functionally dependent on them.

To resolve the issue, use any of the solutions (1 of 3 below)

(1) PHPMyAdmin

Disable: ONLY_FULL_GROUP_BY Mode

If you are using phpMyAdmin, change the sql_mode setting as shown in the screenshot below.

Editsql modeVariable and remove from valueONLY_FULL_GROUP_BYText

or

(2) SQL/Command Prompt

Disable: Disable ONLY_FULL_GROUP_BY mode by running the following command.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

or

(3) Do not use SELECT *

Do not disable ONLY_FULL_GROUP_BY mode, but

Use related columns in SELECT queries. Related means columns that either appear in a group by clause or columns with aggregate functions (MAX, MIN, SUM 代码>, COUNT代码>, etc.)

---

important

Changes made using point(1) OR point(2) will not set it permanently and will be restored after every reboot.

Therefore, you should set it in a configuration file (e.g. /etc/mysql/my.cnf in the [mysqld] section) so that the changes persist across MySQL restarts Effect:

Configuration file:/etc/mysql/my.cnf

Variable name: sql_mode OR sql-mode

Remove the word ONLY_FULL_GROUP_BY from the value and save the file.

Note: If you do not find the sql_mode variable in the configuration file, please insert the following two lines

at the end of the file

[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

Answer 2

this

It can be easily solved by changing the sql mode in MySQL through this command,

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

This works for me too.. I used this because in my project there were many queries like this so I just changed this sql schema to only_full_group_by

Or only include all columns in the GROUP BY clause specified by the SELECT statement. sql_mode can remain enabled.

Thanks...:-)

---

Updated: July 14, 2023

Changing the SQL schema is one solution, but the best practice in structured query languages ​​is still to avoid selecting all (SELECT * ...) columns and instead use an aggregator function on the grouped columns, like @Tim Biegeleisen https://stackoverflow.com/a/41887524/3602846

mentioned in the answer below