MySQL error : sql_mode=only_full_group_by

Question

i have this script where i'm used to do 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 goes wrong ; as i have this error :

1055 - Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mutuelle_bi.M.etat_bs' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

How may i modify my script by the way to solve this problem , given that i shouldn't modify any config file of my sql server . and i should only write scripts

Any Suggestions ??

It isn't entirely clear whether you wan to keep `only_full_group_by`, which can be changed only for current session but is of course a feature to help prevent bogus result sets. — Álvaro González, Dec 15 '16 at 12:21
You can simple use the `ANY_VALUE()` function in the nonaggregated column. See: http://stackoverflow.com/a/40578865/439171 — Italo Borssatto, Jan 17 '17 at 20:06

score 13 · Answer 1 · edited Sep 29 '17 at 14:22

13

You can also try to disable the only_full_group_by setting:

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.

edited Sep 29 '17 at 14:22

hg8

1,082
2
15
28

answered Mar 30 '17 at 13:34

Victor Gazotti

1,773
1
13
9

score 7 · Accepted Answer · answered Dec 15 '16 at 12:19

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;

MySQL error : sql_mode=only_full_group_by

2 Answers2