MySQL Error: sql_mode = only_full_group_by

Question

I have this script where I use a simple projection for a dynamic result

SELECT M.nom_utilisateur, SUM(M.montant_bulletin ) as Montant_Total_BS, SUM(M.montant_payer ) as Montant_Total_payer, COUNT(M.ref_bs ) as nbr_bs_total, (SELECT COUNT(*) FROM mutuelle_bi.`Mutuelle` WHERE nom_utilisateur = M.nom_utilisateur AND (M.nom_assurence = "Star" AND M.etat_bs = "Remboursé")) as nbr_bs_total_payer, (SELECT COUNT(*) FROM mutuelle_bi.`Mutuelle` WHERE nom_utilisateur = M.nom_utilisateur AND (M.nom_assurence = "Star" AND M.etat_bs = "Non remboursé")) as nbr_bs_non_payer, (SELECT COUNT(*) FROM mutuelle_bi.`Mutuelle` WHERE nom_utilisateur = M.nom_utilisateur AND (M.nom_assurence = "Star" AND M.etat_bs = "En cours")) as nbr_bs_en_cours, (SELECT COUNT(*) FROM mutuelle_bi.`Mutuelle` WHERE nom_utilisateur = M.nom_utilisateur AND (M.nom_assurence = "Star" AND M.etat_bs = "Nouveau")) as nbr_bs_nouveau FROM mutuelle_bi.`Mutuelle` M WHERE M.nom_assurence = "Star" GROUP BY M.nom_utilisateur 

but something went wrong; since I have this error:

1055 - Expression No. 5 of the SELECT list is not in the GROUP BY clause and contains the non-aggregated column "mutuelle_bi.M.etat_bs", which is not functionally dependent on the columns in the GROUP BY clause; this is incompatible with sql_mode = only_full_group_by

How can I modify my script to solve this problem, given that I should not modify any configuration file of my SQL server . and I should only write scripts

Any suggestions

Answer 1

I think you just want conditional aggregation:

 SELECT M.nom_utilisateur, SUM(M.montant_bulletin ) as Montant_Total_BS, SUM(M.montant_payer) as Montant_Total_payer, SUM(M.etat_bs = 'Remboursé') as nbr_bs_total_payer, SUM(M.etat_bs = 'Non remboursé') as nbr_bs_non_payer, SUM(M.etat_bs = 'En cours') as nbr_bs_en_cours, SUM(M.etat_bs = 'Nouveau') as nbr_bs_nouveau FROM mutuelle_bi.`Mutuelle` M WHERE M.nom_assurence = 'Star' GROUP BY M.nom_utilisateur; 

Answer 2

You can also disable the only_full_group_by parameter:

 set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 

Worked for me.