AM using MySQL 5.7.13 on my windows PC with WAMP Server
Here my Problem is While executing this query
SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
`status` = 'Active'
GROUP BY `proof_type`
Am getting always error like this
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Can you please tell me the best solution...
I need Result like
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| 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 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
Use any one solution
(1) PHPMyAdmin
if you are using phpMyAdmin then change the "sql_mode" setting as mentionedthe in below screenshot.
Edit "sql mode" variable and remove the "ONLY_FULL_GROUP_BY" text from the value
(2) Command prompt Run the below command.
(3) Don't use SELECT *
Use relevant column in SELECT query. relevant means columns, which are either coming in "group by" clause or column with the aggregate function (MAX, MIN, SUM, COUNT etc)
Important note
Changes made by using point(1) OR point(2) will not set it PERMANENTLY, and it will revert after every restart.
So you should set this in your config file (e.g. /etc/mysql/my.cnf in the [mysqld] section), so that the changes remain in effect after MySQL restart:
Config File: /etc/mysql/my.cnf
Variable name : sql_mode OR sql-mode
When MySQL's
only_full_group_by
mode is turned on, it means that strict ANSI SQL rules will apply when usingGROUP BY
. With regard to your query, this means that if youGROUP BY
of theproof_type
column, then you can only select two things:proof_type
column, orBy "aggregates" of other columns, I mean using an aggregate function such as
MIN()
,MAX()
, orAVG()
with another column. So in your case the following query would be valid:The vast majority of MySQL
GROUP BY
questions which I see on SO have strict mode turned off, so the query is running, but with incorrect results. In your case, the query won't run at all, forcing you to think about what you really want to do.Note: The ANSI SQL extends what is allowed to be selected in
GROUP BY
by also including columns which are functionally dependent on the column(s) being selected. An example of functional dependency would be grouping by a primary key column in a table. Since the primary key is guaranteed to be unique for every record, therefore the value of any other column would also be determined. MySQL is one of the databases which allows for this (SQL Server and Oracle do not AFAIK).You can solve it by changing the sql mode with this command:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
and ... remember to reconnect the database!!!
This
will be simply solved by changing the sql mode in MySQL by this command,
This is too works me.. I used this becz in my project there are many Queries like this so just change this sql mode onli_full_group_by
ThanQ... :-)
You can disable sql_mode=only_full_group_by by some command you can try this by terminal or MySql IDE
Open you WAMP panel and open MySQL configuration file. In it search for "sql_mode" if you find it set it to "" else if you don't find it add sql_mode="" to the file.
Restart the MySQL server and you are good to go...
happy coding.