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
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).
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', ''));