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

reply all (2)

P粉5236250802023-08-21 10:10:51 2 floor

There is a system variableONLY_FULL_GROUP_BYin the MySQL engine.

Starting with MySQL version 5.7.5:ONLY_FULL_GROUP_BYSQL mode is enabled by default.

Prior to version 5.7.5:ONLY_FULL_GROUP_BYis not enabled by default.

IfONLY_FULL_GROUP_BYSQL mode is enabled (enabled by default starting with version 5.7.5), MySQL will reject query select lists,HAVINGconditions, orORDER BYThe list references non-aggregate columns that are neither named in theGROUP BYclause 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 thesql_modesettings according to the screenshot below.

Editsql modeVariable and remove from valueONLY_FULL_GROUP_BYText

(2) SQL/Command Prompt

Disableby running the following command:ONLY_FULL_GROUP_BYmode.

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

(3) Do not useSELECT *

Do not disableONLY_FULL_GROUP_BYmode, instead

Use related columns inSELECTqueries. Related columns refer to columns that appear in thegroup byclause or have aggregate functions (MAX,MIN,SUM,COUNTetc.) column

important hint

Changes made usingpoint (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.cnffile) so that in MySQL To keep changes in effect after a reboot:

Configuration file:/etc/mysql/my.cnf

Variable name:sql_modeorsql-mode

Remove the wordONLY_FULL_GROUP_BYfrom the value and save the file.

Note: If thesql_modevariable 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

Like+0

Add Reply

P粉1210816582023-08-21 00:49:48 1 floor

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

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 inhttps://stackoverflow.com/a/41887524/3602846

mentioned in the answer below