SUM et group by

Version imprimable

23/03/2020, 23h18
yurispy

1 pièce(s) jointe(s)

SUM et group by

Bonjour,

j'ai une db qui me retourne ceci
Pièce jointe 548628

Ce que j'aurais souhaité faire est un select de tout mais que si d'en merge_id il y a un ID identique a d'autre lignes alors je fais la somme de total, surface et moyenne de dose ha.

auriez vous des pistes?

Merci
24/03/2020, 07h37
tatayo
Bonjour,
Est-ce que veux garder les autres colonnes ?
Si oui, il faut une sous-requête qui calcule les sommes en regroupant sur l'ID, et dans la requête principale une jointure avec cette sous-requêne.
Sinon, une simple requête avec des SUM fera l'affaire.
Par exemple:
Code:

1 2 3 4 5 6 7 8 select t.IS,t.col1,s.tot1,s.tot2 from MaTable t inner join ( select id,sum(col4),sum(col5) from MaTable t group by t.id) s on s.id = t.id )
Evidemment la sous-requête peut très bien taper sur ne autre table.

Tatayo.
P.S. MySql permet de ne pas spécifier toutes les colonnes du SELECT qui ne font pas l'objet d'une agrégation (sum,avg,min...) dans la clause GROUP BY, mais c'est une aberration oublier très vite.
Rien ne garantie quelles valeur vont être envoyées, c'est au petit bonheur la chance.
24/03/2020, 11h47
escartefigue

Citation:

Envoyé par tatayo

P.S. MySql permet de ne pas spécifier toutes les colonnes du SELECT qui ne font pas l'objet d'une agrégation (sum,avg,min...) dans la clause GROUP BY, mais c'est une aberration oublier très vite.
Rien ne garantie quelles valeur vont être envoyées, c'est au petit bonheur la chance.

:plusser: mais avec une coquille : Mysql permet de spécifier dans le select des colonnes qui ne font pas l'objet d'une agrégation ;)

Il existe un paramètre ONLY_FULL_GROUP_BY qui permet de corriger cette aberration, mais avant la version 5.7.5 de Mysql, ce paramètre était inactif, autorisant donc des sélections sans cohérence avec le regroupement :aie:
24/03/2020, 14h25
tatayo

escartefigue:
J'avoue que ma formulation n'était pas forcément très claire.
En fait j'aurai du tourner ma phrase dans l'autre sens:
MySql permet de ne pas spécifier dans la clause GROUP BY toutes les colonnes du SELECT qui ne font pas l'objet d'une agrégation (sum,avg,min...).

En gros tu dis que MySql permet de mettre dans le select des colonnes non agrégées qui ne sont pas dans le group by, et je dis que MySQL n'oblige pas à mettre dans le group by les colonnes du select qui ne sont pas agrégées.
Mais dans le fond on dit la même chose ;).

Et spécial dédicace à celui qui a eut l'idée de faire ça dans MySQL : :sm::fessee:

Tatayo.
24/03/2020, 15h20
yurispy

Cela supprime les lignes qui on NULL dans la colonne multi_id_traitement_phyto alors que je souhaite les conserver.

Moi je veux que les null reste comme ça et que celles qui on des id identiques, on les regroupe en une seule et l'on face la somme.
24/03/2020, 16h10
escartefigue

Si l'on souhaite conserver les lignes sans correspondance dans la table dérivée, alors il faut remplacer la jointure INNER par une jointure OUTER ;)
24/03/2020, 21h46
yurispy

Ok merci, cela fonctionne presque comme je le souhaite
j'ai donc mais ligne NULL, j'ai mais calcul
mais dernier hic, les lignes ou il calcul il ne les groupes pas en 1 seule ligne? Est ce possible?
25/03/2020, 07h23
tatayo

Bonjour,
Tu peux nous donner ta requête, un jeu de test, le résultat attendu et ce que tu obtiens ?
Ainsi il n'y aura pas d'erreur d'interprétation.

Tatayo.

2 pièce(s) jointe(s)

La table et quelque donnée dedans Pièce jointe 553830
mais il y a que la table principale sans toute les jointures

Actuellement ma requête la voici

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SELECT
	t.*, fiche_culture.*, produit.*, operateur.*, justification.*, DATE_FORMAT(t.date, '%d/%m/%Y') AS date_fr,
	DATE_FORMAT(t.date_recolte, '%d/%m/%Y') AS date_recolte_fr,
	SCA_travaux.nom,
	SCA_travaux.operation,
	SCA_travaux.date_debut_intervention,
	DATE_FORMAT(
		SCA_travaux.date_debut_intervention,
		'%d/%m/%Y'
	) AS SCA_date_fr,
	SCA_travaux.outil,
	SCA_travaux.tracteur,
	SCA_travaux.numero_intervention,
	s.*
FROM
	travaux t
LEFT JOIN fiche_culture ON t.fiche_culture_id = fiche_culture.id_fiche_culture
LEFT JOIN produit ON t.produit_id = produit.id
LEFT JOIN operateur ON t.operateur_id = operateur.id
LEFT JOIN justification ON t.travaux = justification.id
LEFT JOIN SCA_travaux ON t.SCA_id = SCA_travaux.id
LEFT OUTER JOIN (
	SELECT
		t.supp,
		t.merge_id,
		t.produit_id,
		SUM(t.total) AS SUM_total,
		SUM(t.surface) AS SUM_surface,
		AVG(t.dose_ha) AS AVG_dose_ha
	FROM
		travaux t
	WHERE
		t.supp = 0
	AND t.fiche_culture_id = 519
	GROUP BY
		t.merge_id
) s ON s.merge_id = t.merge_id
WHERE
	t.fiche_culture_id = 519
AND t.supp = 0
ORDER BY
	`t`.`date`,
	`t`.`id_travaux` ASC

Elle fonctionne comme je veux mais il manque une condition dans la somme.
Cela fait comme ce qui est écrit une somme de toute les lignes ou merge_id est identique
Moi je voudrais qu'il y est un 2nd paramètre, le produit_id
Pièce jointe 553827

Comme on te le disait plus haut, ici le problème de MySQL est sa gestion "calamiteuse" du group by.
Et je pense que le soucis vient de là, dans la sous-requête:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 ... SELECT t.supp, t.merge_id, t.produit_id, SUM(t.total) AS SUM_total, SUM(t.surface) AS SUM_surface, AVG(t.dose_ha) AS AVG_dose_ha FROM travaux t WHERE t.supp = 0 AND t.fiche_culture_id = 519 GROUP BY t.merge_id ...
Tu as 3 colonnes dans le SELECT, et seulement 1 dans le GROUP BY. Donc on ne sait pas ce que MySQL va garder comme valeur pour les deux autres colonnes, dont le produit_id qui semble poser problème.

Petite question en passant: j'ai peut-être mal analysé la requête, mais pourquoi n'utilises-tu pas le pas la colonne product_id dans la jointure entre t et s ?

Tatayo.

P.S. et supprime les * dans le SELECT, le jour où tu fais évoluer la structure de tes tables le résultat de la requête va changer lui aussi.

raahh ça m'énerve de pas y avoir pensé. ça semble fonctionner correctement.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SELECT
	t.*, fiche_culture.*, produit.*, operateur.*, justification.*, DATE_FORMAT(t.date, '%d/%m/%Y') AS date_fr,
	DATE_FORMAT(t.date_recolte, '%d/%m/%Y') AS date_recolte_fr,
	SCA_travaux.nom,
	SCA_travaux.operation,
	SCA_travaux.date_debut_intervention,
	DATE_FORMAT(
		SCA_travaux.date_debut_intervention,
		'%d/%m/%Y'
	) AS SCA_date_fr,
	SCA_travaux.outil,
	SCA_travaux.tracteur,
	SCA_travaux.numero_intervention,
	s.*
FROM
	travaux t
LEFT JOIN fiche_culture ON t.fiche_culture_id = fiche_culture.id_fiche_culture
LEFT JOIN produit ON t.produit_id = produit.id
LEFT JOIN operateur ON t.operateur_id = operateur.id
LEFT JOIN justification ON t.travaux = justification.id
LEFT JOIN SCA_travaux ON t.SCA_id = SCA_travaux.id
LEFT OUTER JOIN (
	SELECT
		t.supp,
		t.merge_id,
		t.produit_id,
		SUM(t.total) AS SUM_total,
		SUM(t.surface) AS SUM_surface,
		AVG(t.dose_ha) AS AVG_dose_ha
	FROM
		travaux t
	WHERE
		t.supp = 0
	AND t.fiche_culture_id = 519
	GROUP BY
		t.merge_id, t.produit_id
) s ON s.merge_id = t.merge_id
WHERE
	t.fiche_culture_id = 519
AND t.supp = 0
GROUP BY id_travaux
ORDER BY
	`t`.`date`,
	`t`.`id_travaux` ASC

J'ai du rajouté le GROUP BY id_travaux car sans il multiplié les lignes a la fin, étrange.

les * sont retirés quand j’intègre ma requête dans le code oui, sinon j'ai trop de colonne qui ne servent à rien. Mais quand je teste et construit oui je mets souvent *

Merci beaucoup.