SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated

when i got upgraded my ubuntu from 15.10 to 16.04 i have this erro in my yii2 project

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3
of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'iicityYii.opportunity_conditions.money' which is not functionally dependent 
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The SQL being executed was:

SELECT SUM(oc.money),op.id,oc.money,
            op.mantaghe,
            op.`time`, op.`id`, `op`.`logo`,
           `pd`.`user_id`, `op`.`name`, 
           `pd`.`co_name`, `op`.`address`, 
           `op`.`project_type_id`, `op`.`state_id`
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

how to solve my problem ?

Run:

sudo mysql -u root -p
mysql> SELECT @@global.sql_mode;

(Then optionally copy the output to your notes somewhere in case you want to revert to those original settings later.)

And change the SQL Mode for your MySQL Server Instance:

mysql> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

(If you ever want to roll back, you could run something like mysql> SET GLOBAL sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; using the value you saved.)

---

A more permanent way (which will survive restarts of MySQL) would be using the MySQL configs. Go to /etc/mysql/my.cnf (or you may need to run sudo vim /etc/mysql/mysql.conf.d/mysql.cnf):

• Add a section for [mysqld] and right below it add the statement sql_mode = "" or something like sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION".

• Restart the MySQL service:

   sudo systemctl restart mysql
  

(or sudo service mysql restart)

See also https://dba.stackexchange.com/a/113153/18098

In laravel with MySql go to file config/database.php and it change in array MySql mode strict to false.

'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => false, //from true
        'engine' => null,
    ],
],

The solution is to edit the MySQL config file because the config will revert after every restart...

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

and add

[mysqld]
sql-mode=""

then restart

sudo systemctl restart mysql

Works on ubuntu 18.04.

In select you have an aggregate function named sum and a set of column name, the error tell you that you have not specified the correct list of column name in group by clause. could be you should add more columns name in group by probably related to the profile_details, opportunity_conditions table

You have also ,(opportunity.id),(opportunity_conditions.money), (opportunity.mantaghe), why the () if you need sum you must add sum to all column

sum(opportunity.id), sum(opportunity_conditions.money),

sum(opportunity.mantaghe),

otherwise if this are normal columns you should use the normal syntax without ()

opportunity.id, opportunity_conditions.money,opportunity.mantaghe,

I have tried to rewrite a possible query

 SELECT SUM(opportunity_conditions.money),
        `opportunity`.`id`,
        `opportunity_conditions.money`,
        `opportunity.mantaghe`, 
        `opportunity`.`time`, 
        `opportunity`.`logo`, 
        `profile_details`.`user_id`,
        `opportunity`.`name`, 
        `profile_details`.`co_name`,
        `opportunity`.`address`, 
        `opportunity`.`project_type_id`,
        `opportunity`.`state_id` 
FROM `opportunity` 
INNER JOIN `profile_details` ON `opportunity`.`user_id`= `profile_details`.`user_id` 7
INNER JOIN `opportunity_conditions` ON `opportunity`.`id`=`opportunity_conditions`.`opportunity_id` 
GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  
ORDER BY `opportunity`.`id` DESC

with group by on the essential column name (i hope)

GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  

Please just copy this line and run it. it worked for me.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Youtube video to fix this

Simply set strict false on following

Config > database.php  set 'strict' => false,

For CentOS8 I use webmin config file /etc/my.cnf.d/mysql-server.cnf

[mysqld] sql-mode=""

It is a good idea to update your query to:

    SELECT SUM(any_value(oc.money)),op.id,any_value(oc.money) as money,
            op.mantaghe,
            op.`time`, op.`id`, `op`.`logo`,
           any_value(`pd`.`user_id`) as user_id, `op`.`name`, 
           any_value(`pd`.`co_name`) as co_name, `op`.`address`, 
           `op`.`project_type_id`, `op`.`state_id`
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

Understanding
When your query contains group by you should only select columns from the table you are grouping by, but if not, you ought to use the any_value() function to call the columns from other table.

I personally try not to update the sql_mode global variable.

You can read more about it on https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Thx, this helped to me, but this will not set it PERMANENTLY, and it will revert after every restart.

So you should set this in your config file (e.g. /etc/mysql/my.cnf in the [mysqld] section), so that the changes remain in effect after a MySQL restart:

Config File: /etc/mysql/my.cnf

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

You can use ANY_VALUE for the non-aggregated columns. So, you could use:

SELECT SUM(oc.money),
       op.id,
       ANY_VALUE(oc.money),
       ANY_VALUE(op.mantaghe),
       ANY_VALUE(op.`time`), op.`id`, ANY_VALUE(`op`.`logo`),
       ANY_VALUE(`pd`.`user_id`), ANY_VALUE(`op`.`name`), 
       ANY_VALUE(`pd`.`co_name`), ANY_VALUE(`op`.`address`), 
       ANY_VALUE(`op`.`project_type_id`), ANY_VALUE(`op`.`state_id`)
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

More details here.

in laravel

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'turn.barnamehs.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by select * from tables where date(end_date) >= 2022-05-10 00:00:00 group by speciality_id

> solution: change 'strict' => false, in config/database.php