SUM dans un group_concat [Résolu]

[David55]

Bonjour à tous et à toute.

Voici mes tables:
commande:
_ id
_ date

jouets_commandes:
_ id
_ jouet_id
_ commande_id
_ quantite

jouets:
_ id
_ ordre
_ libelle

Voici ma requete:

Code :

1
2
3
4
5
6
7
 
SELECT CMD.id,
       CMD.date,
       GROUP_CONCAT(DISTINCT J.libelle, ' (', CONVERT(JCMD.quantite, CHAR(8)), ')' ORDER BY J.ordre SEPARATOR '<br/>') AS detail
FROM commandes CMD
    INNER JOIN jouets_commandes JCMD ON CMD.id = JCMD.commande_id
    INNER JOIN jouets J ON J.id = JCMD.jouet_id

En faite, j'aimerai faire la somme de mes quantités dans mon group_concat.
C'est à dire écrire quelque chose comme ceci:

Code :

1
2
 
GROUP_CONCAT(DISTINCT J.libelle, ' (', CONVERT(SUM(JCMD.quantite), CHAR(8)), ')' ORDER BY J.ordre SEPARATOR '<br/>') AS detail

Cependant, ceci n'est pas possible avec MySQL.

Quelqu'un aurait une solution?

[David55]

J'ai trouvé une solution:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT CMD.id,
       CMD.date,
       GROUP_CONCAT(TMP.libelle, ' (', CONVERT(TMP.quantite, CHAR(8)), ')' ORDER BY TMP.ordre SEPARATOR '<br/>') AS detail
FROM commandes CMD
    INNER JOIN jouets_commandes JCMD ON CMD.id = JCMD.commande_id
    INNER JOIN jouets J ON J.id = JCMD.jouet_id
    INNER JOIN
(
SELECT CMD.id AS cmd,
       J.id AS jouet,
       J.libelle,
       SUM(JCMD.quantite) AS quantite,
       J.ordre
FROM commandes CMD
    INNER JOIN jouets_commandes JCMD ON CMD.id = JCMD.commande_id
    INNER JOIN jouets J ON J.id = JCMD.jouet_id
GROUP BY J.id
)TMP ON TMP.cmd = CMD.id AND TMP.jouet = J.id

[vmolines]

Vous donnez bien la structure des tables mais pas le résultat que vous souhaitez obtenir. Vous ne risquez pas d'être aidé.

[David55]

Vous avez raison!
En faite je voulais passer de :

Code :

1
2
3
4
5
6
 
poupee 1
poupee 1
poupee 1
voiture 3
voiture 1

a:

Code :

1
2
 
poupee (3) <br/> voiture (4)

(concaténer les 5 ligne en 1)

[vmolines]

Faire la somme des quantités par article et par commande :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT 
	COMMANDE.ID AS CMD_ID, 
	COMMANDE.DATE AS CMD_DATE, 
	JOUETS.ID AS JOU_ID, 
	JOUETS.LIBELLE AS JOU_LIBELLE, 
	SUM(JOUETS_COMMANDES.QUANTITE) AS JOUCMD_QTE
FROM COMMANDE 
	INNER JOIN JOUETS_COMMANDES 
		ON COMMANDE.ID = JOUETS_COMMANDES.COMMANDE_ID
		INNER JOIN JOUETS 
			ON JOUETS_COMMANDES.JOUET_ID = JOUETS.ID
GROUP BY 
	COMMANDE.ID,
	COMMANDE.DATE, 
 	JOUETS.ID, 
	JOUETS.LIBELLE

Attention, vous faîtes plusieurs choses de travers :

- vous utilisez le SGBD pour faire de la présentation (lignes en colonnes, formattage du résultat)
- vous nommez mal vos tables (mélange singulier/pluriel, utilisation de mots clés reservés SQL comme "DATE" pour une colonne, disparité des noms de colonne dans la table de base et la table référencée, ...)

Utilisez votre base de données et SQL pour ramener les informations brutes qui vous intéressent et exploitez le résultat dans la partie client/code pour tout ce qui est affichage. Ici une simple boucle sur le résultat.

[David55]

Merci de votre réponse.
En effet, j'utilise le SGBD pour faire de la présentation car je n'ai pas le choix (j'utilise le logiciel BIRT pour traiter le résultat et il ne me propose pas de concaténer comme je le souhaite)!

Au sujet des noms des tables, ce n'est pas les vrais nom, je les ai juste changée pour confidentialité mais la structure est la même.

Donc votre requête résume bien ce que je veux faire mais elle me renvoie plusieurs ligne alors que j'en veux qu'une!

Elle me renverait:

Code :

1
2
3
 
poupee 3
voiture 4

[vmolines]

Je ne connais pas BIRT mais je suis étonné que cette solution de reporting ne permette pas de traiter les lignes de la source de données. Demandez quand même des précisions à des connaisseurs de BIRT, le besoin me semble trivial.

Il y a une bonne mauvaise façon de faire ce que vous souhaitez en SQL mais j'attends d'avoir votre retour par rapport à BIRT ou que vous trouviez la solution tout seul.

[David55]

Oui en effet, il y a bien des solutions avec BIRT. Cependant, mon rapport est terminé et il me manque juste cette information. Cela me prendrais ma journée entière pour le recommencer avec cette donnée.
Donc en attendant d'avoir le temps de recommencer, je cherche une solution intermédiaire comme je l'ai montré plus haut !

PS: j'ai deja trouvé une solution (cf 2eme poste), mais j'ai l'impression qu'elle n'ai pas optimal!

[vmolines]

J'aurais fait comme ça (basé sur ma première solution en tant que sous requête, le group_concat en plus) :

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
SELECT 
	CMD2.CMD_ID, 
	CMD2.CMD_DATE, 
	GROUP_CONCAT(CONCAT( JOU_LIBELLE, 
						' (', 
						JOUCMD_QTE, 
						')'
						) 
						SEPARATOR ', ') AS CMD_LISTE
FROM (	SELECT 
			COMMANDE.ID AS CMD_ID, 
			COMMANDE.DATE AS CMD_DATE, 
			JOUETS.ID AS JOU_ID, 
			JOUETS.LIBELLE AS JOU_LIBELLE, 
			SUM(JOUETS_COMMANDES.QUANTITE) AS JOUCMD_QTE
		FROM COMMANDE 
			INNER JOIN JOUETS_COMMANDES 
				ON COMMANDE.ID = JOUETS_COMMANDES.COMMANDE_ID
				INNER JOIN JOUETS 
					ON JOUETS_COMMANDES.JOUET_ID = JOUETS.ID
		GROUP BY 
			COMMANDE.ID,
			COMMANDE.DATE, 
			JOUETS.ID, 
			JOUETS.LIBELLE) AS CMD2
GROUP BY 
	CMD2.CMD_ID, 
	CMD2.CMD_DATE

J'avoue ne pas savoir quelle version est la moins pire. Une petite analyse du plan d'exécution des deux requêtes serait pertinente.