The columns in the SELECT statement are not in the GROUP BY clause and contain non-aggregated columns....Incompatible with sql_mode=only_full_group_by

Question

I'm using MySQL 5.7.13 with WAMP server on my 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 get this error

Expression #1 in the SELECT list is not in the GROUP BY clause and contains the non-aggregate column 'returntr_prod.tbl_customer_pod_uploads.id' which has no functional dependencies on the columns in the GROUP BY clause; this is consistent with sql_mode = only_full_group_by is not compatible

Please tell me the best solution.

The results I need are as follows

 ---- --------- --------- --------- -- -------- ---------- ------------ --------------- ---- ---------- ------------ -------- -------------------- -------------------------
| 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 with Mysql version 5.7.5 : ONLY_FULL_GROUP_BY SQL 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 query select lists, HAVING conditions, or ORDER BY The list references non-aggregate columns that are neither named in the GROUP BY clause nor are they dependent on them.

To resolve this issue, use one of the following three solutions

(1) PHPMyAdmin

Disable: ONLY_FULL_GROUP_BYMode

If you are using phpMyAdmin, please change the sql_mode settings according to the screenshot below.

Editsql modeVariable and remove from valueONLY_FULL_GROUP_BYText

or

(2) SQL/Command Prompt

Disable by running the following command: ONLY_FULL_GROUP_BY mode.

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, instead

Use related columns in SELECT queries. Related columns refer to columns that appear in the group by clause or have aggregate functions (MAX, MIN, SUM, COUNT etc.) column

---

important

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

Therefore, you should set this in the configuration file (for example, in [mysqld] section /etc/mysql/my.cnf) so that when MySQL restarts Changes still take effect after startup:

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 the sql_mode variable is not found in the configuration file, please insert the following 2 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

This problem will be solved simply by changing the SQL mode in MySQL, using the following command:

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

Works for me too... I used this because in my project I had a lot of similar queries so I just changed this SQL mode to only_full_group_by

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

Thanks... :-)

---

Update date: July 14, 2023

Changing the SQL schema is a workaround, but best practice for structured query languages ​​would be to avoid selecting all (SELECT *...) columns and instead use aggregate functions on grouped columns like @Tim Biegeleisen https://stackoverflow.com/a/41887524/3602846

mentioned in the answer below