mysql – SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by

mysql – SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by

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

will be simply solved by changing the sql mode in MySQL by this command,

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

This too works for me..
I used this, because in my project there are many Queries like this so I just changed this sql mode to only_full_group_by

OR simply include all columns in the GROUP BY clause that was specified by the SELECT statement. The sql_mode can be left enabled.

Thank You… 🙂

When MySQLs only_full_group_by mode is turned on, it means that strict ANSI SQL rules will apply when using GROUP BY. With regard to your query, this means that if you GROUP BY of the proof_type column, then you can only select two things:

  • the proof_type column, or
  • aggregates of any other column

By aggregates of other columns, I mean using an aggregate function such as MIN(), MAX(), or AVG() with another column. So in your case the following query would be valid:

SELECT proof_type,
       MAX(id) AS max_id,
       MAX(some_col),
       MIN(some_other_col)
FROM tbl_customer_pod_uploads
WHERE load_id = 78 AND
      status = Active
GROUP BY proof_type

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 wont 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).

mysql – SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by

There is a system variable ONLY_FULL_GROUP_BY in MySql engine.

From Mysql Version 5.7.5: ONLY_FULL_GROUP_BY SQL mode is enabled by default

Before Version 5.7.5: ONLY_FULL_GROUP_BY was not enabled by default.

If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default from version 5.7.5), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to non-aggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

To sort out the issue, use any one solution (out of below 3)

(1) PHPMyAdmin

Disable: ONLY_FULL_GROUP_BY mode

if you are using phpMyAdmin then change the sql_mode setting as mentioned in the below screenshot.
enter

Edit sql mode variable and remove the ONLY_FULL_GROUP_BY text from the value

OR

(2) SQL/Command prompt

Disable: ONLY_FULL_GROUP_BY mode by running the below command.

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

OR

(3) Dont use SELECT *

Do not disable the ONLY_FULL_GROUP_BY mode but

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

Remove word ONLY_FULL_GROUP_BY from the value and save the file.

Note: If you have not found sql_mode variable in the config file than please insert below 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

Leave a Reply

Your email address will not be published.