"The non-aggregated columns in the SELECT statement do not appear in the GROUP BY clause and are incompatible with sql_mode=only_full_group_by"

Question

I am 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 dependency on the column 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 relevant to their functionality.

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

(1) PHPMyAdmin

Disable: ONLY_FULL_GROUP_BYMode

If you are using phpMyAdmin, 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 hint

Changes made using point (1) or point (2) are not permanent settings and will be restored after each restart.

Therefore, you should set this in the configuration file (for example, in the [mysqld] section of the /etc/mysql/my.cnf file) so that in MySQL To keep changes in effect after a reboot:

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 problem can 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',''));

It works for me too...

I used this method because there are many similar queries in my project, so I just changed the 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 ​​is to avoid selecting all (SELECT *...) columns and instead use aggregate functions on the grouping columns, as @Tim Biegeleisen does in https://stackoverflow.com/a/41887524/3602846

mentioned in the answer below