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:

SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

That’s all about how to solve the problem that occurs in the SQL query which is having Group By clause.