Video Tutorial only_full_group_by

You are trying to make a GROUP BY and you are experiencing the following error:

Expression # 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXXXX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode = only_full_group_by

The cause

To understand the source of this error the best is a small example.
Let's take a list of people:

id | firtname | lastname
12 | John | Doe
14 | Jane | Doe
20 | Marc | Boher
50 | Marie | Lafore

And let's try the following query:

SELECT firstname, lastname FROM users BY GROUP lastname

The system will group the data by lastname but there are several named users Doe. The tool will have to choose a line to return for the first time and do not know which data to return. Without the SQL mode ONLY_FULL_GROUP_BY it is content to return a line (without a specific logic for the choice of the line) which can lead to inconsistencies. With the mode ONLY_FULL_GROUP_BY it simply returns an error to mean the problem.

But I did not have that mistake before?

If you are wondering why you did not have this error before, this is due to a change of the default SQL modes and the activation of ONLY_FULL_GROUP_BY (from version 5.7 of MySQL).

The solution

To remedy the problem you can use aggregation functions to express the result to retrieve.

SELECT MIN (firstname), lastname FROM users GROUP BY lastname

If you want to keep the previous behavior as part of MySQL you can use the aggregation function ANY_VALUE ().

A second option is to modify the sql modes to remove this restriction (I advise, however, to correct the SQL queries rather than rely on the configuration of your DBMS).

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