Tutorials
Only full group by when executing a query in MySql 5.7
MySQL 8
Run the following query in the database
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
MySQL 5.7
When SELECTing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how to make those multiple values into one value. Commonly, this is done with an aggregate function like COUNT(), SUM(), MAX() etc... I say usually because most other popular database systems insist on this. However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value! It also has an ANYVALUE() function that could be used as another solution to this question if you really needed the same behaviour as before. This flexibility comes at a cost because it is non-deterministic, so I would not recommend it unless you have a very good reason for needing it. MySQL are now turning on the onlyfullgroupby setting by default for good reasons, so it's best to get used to it and make your queries comply with it.
It's also good practice to qualify all the columns with their table name or alias when there's more than one table involved...
If you don't want to make any changes in your current query then follow the below steps
- ssh into your box
- Type:
sudo vim /etc/mysql/mysql.conf.d/mysqld.conf - Scroll to the bottom of file and type A to enter insert mode
Add the following line in [mysqld] section:
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Now save the file and restart the mysql server.