'Group by incompatible google sql cloud

I did an experiment to create multiple queries on Google SQL cloud, as shown in the picture, but the result is

ERROR 1055 (42000): Expression # 5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ipol.sales.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode = only_full_group_by

Solution 1:^[1]

In cloud SQL instance go to Edit option and Add Database Flag SQL mode - Traditional

Solution 2:^[2]

you cannot SET GLOBAL unless you're logged in as root (or equivalent) user.

and also, the GROUP BY clause is invalid. fix the statement before running it

... whatever Expression # 5 may be (just count them).

this comes from sql_mode=only_full_group_by.

Solution 3:^[3]

if you run this query in your database

select version(),@@sql_mode;

you will get

version()	@@sql_mode
8.0.26-google	ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

the ONLY_FULL_GROUP_BY in the @@sql_mode caused the problem.

edit your Cloud SQL via GCP GUI page

add flag sql_mode, select all of the value in the @@sql_mode except ONLY_FULL_GROUP_BY and save.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Robert Robinson
Solution 2
Solution 3

'Group by incompatible google sql cloud

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]