sql_mode=only_full_group_by

Im trying to help someone to implement new function on his code; but I have this error

Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'br_interne.questionnaires_reponses.re_int' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT `ch_ref` AS id, `ch_date_prevue` AS date, `ch_prestataire_terrassement_branchement` AS prestataire, `re_int` FROM (`questionnaires_contacts`) JOIN `chantiers_osr` ON `ch_ref`=`qc_chantier_id` JOIN `communes` ON `ch_insee`=`co_insee` JOIN `departements` ON `co_departement`=`de_id` LEFT JOIN `questionnaires_reponses` ON `re_questionnaire_contact` = `qc_id` AND re_question = 7 WHERE `de_id` = '78' AND `ch_date_prevue` >= '0000-00-00' AND `qc_chantier_type` = 'osr' AND `qc_invitation_envoyee` = '1' GROUP BY `qc_chantier_id`

Unfortunately if change the SQL_mode the request doesn’t work. ( really strange because this code works on the server ).

If you have any idea. Even if with the documentation of SQL I can find the solution to modify this request.

Thanks a lot in advance.

Answer

Remove ONLY_FULL_GROUP_BY from MySql console

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

OR

Fire below query in PHPMyAdmin

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