You might encounter the following issue while executing an SQL query qith Group By clause in MySQL database server.
Error Type: mysqli_sql_exception #1055
Error Details: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘roytuts.visitors.access_date’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The same issue which given in the above text is shown in the following image:
The above error occurred while I was trying to run a query with Group By clause in MySQL server 8.0.17/8.0.22. This problem occurs due to the fact that a value which was set to the sql_mode variable is preventing the successful execution of the query.
First you may want to check the value of the sql_mode variable by executing the following command from your MySQL client window:
show variables like "sql_mode";
Whatever value you get using the above command you can take a backup of the value.
Next is to set the value to the sql_mode using the following command. This will solve the problem and re-running of your SQL query would give you the result.
set sql_mode=''; SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Another solution is to set the following value to the sql_mode:
That’s all about how to solve the problem that occurs in the SQL query which is having Group By clause.