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 | +----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
There is a system variable
ONLY_FULL_GROUP_BYin the MySql engine.Starting from Mysql version 5.7.5:
ONLY_FULL_GROUP_BYSQL mode is enabled by defaultPrior to version 5.7.5:
ONLY_FULL_GROUP_BYis not enabled by default.If
ONLY_FULL_GROUP_BYSQL mode is enabled (enabled by default starting with version 5.7.5), MySQL will reject select lists,HAVINGconditions, orORDER BYlists Refers to non-aggregate columns that are neither named in aGROUP BYclause nor functionally dependent on them.To resolve the issue, use any of the solutions (1 of 3 below)
(1) PHPMyAdmin
Disable:
ONLY_FULL_GROUP_BYModeIf you are using phpMyAdmin, change the
sql_modesetting as shown in the screenshot below.Edit
sql modeVariable and remove from valueONLY_FULL_GROUP_BYTextor
(2) SQL/Command Prompt
Disable: Disable
ONLY_FULL_GROUP_BYmode by running the following command.or
(3) Do not use
SELECT *Do not disable
ONLY_FULL_GROUP_BYmode, butUse related columns in
SELECTqueries. Related means columns that either appear in agroup byclause 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.cnfin the[mysqld]section) so that the changes persist across MySQL restarts Effect:Configuration file:
/etc/mysql/my.cnfVariable name:
sql_modeORsql-modeRemove the word
ONLY_FULL_GROUP_BYfrom the value and save the file.Note: If you do not find the
at the end of the filesql_modevariable in the configuration file, please insert the following two linesthis
It can be easily solved by changing the sql mode in MySQL through this command,
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