#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by

my Query :

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) GROUP BY libelle order by libelle asc
  • Mysql 5.7.9 runs the query without any problem but mysql 5.7.12 display the error above Could you please help me with that

I had a struggle getting this to work i've tested it and it's working on lamp server mysql version 5.12

So, steps to success:

  1. sudo vim /etc/mysql/conf.d/mysql.cnf
  2. Scroll to the bottom of file Copy and paste

    [mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

to the bottom of the file

  1. save and exit input mode
  2. sudo service mysql restart to restart MySQL.

Done!


This worked for me:

mysql -u root -p
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

You might need sudo for the first step:

sudo mysql -u root -p