Code pour un sous-calcul

Version imprimable

15/11/2022, 13h52
awa123

1 pièce(s) jointe(s)

Code pour un sous-calcul
Bonjour,

j'ai une base de données (tableau noir) à partir de laquelle j'essaie d'afficher les résultats du tableau vert pour chaque numéro de commande.

mais ce tableau de resultat necessite un sous calcul au sein du num commande qui me complique la vie... j'aimerai en effet distinguer les valeurs pour les "familles" qui ont au moins 2 lignes (j'ai illustré cela avec mes 2 colonnes en jaune à droite)

Pièce jointe 628996

le calcul basique est :
Code:

1 2 3 4 select num_commande, sum(ligne), count(distinct code_article) , count(distinct famille) from Table_commande group by num_commande
mais je ne sais pas comment ajouter le sous calcul illustré...

merci de votre aide
15/11/2022, 14h01
al1_24
Bonjour,

Une des manières de résoudre ce problème :
1. Calculer la somme des lignes pour chaque commande ayant plus d'une ligne par famille
2. Faire une jointure sur le numéro de commande avec la première requête
15/11/2022, 14h23
awa123

merci de votre aide

Justement je bloque sur le point 1.
Je ne sais pas comment faire dans le code car je veux un regroupement par num commande dans le tableau de résultat mais avec des colonnes supplémentaires qui distingueront les familles > 1 ligne au sein de cette commande.

merci d'avance
15/11/2022, 15h05
al1_24
Il faut procéder par étapes et préparer d'abord la sous-requête §1

Citation:

Calculer la somme des lignes pour chaque commande ayant plus d'une ligne par famille

J'aurais du détailler :
1. Somme des lignes par commande et par famille
2. Une piste supplémentaire : En anglais, ayant se traduit par ...
La théorie : Formez des sous ensembles avec GROUP BY - Agrégez avec SUM, COUNT, MAX, MIN, AVG... - Filtrer les agrégats
15/11/2022, 17h50
awa123
merci al1_24

effectivement avec le code ci-dessous j'arrive à avoir le détail filtré uniquement sur ce que je veux
Code:

1 2 3 4 5 6 select num_commande, sum(ligne), count(distinct code_article) from Table_commande group by num_commande, famille having sum(ligne) > 1
neanmoins, maintenant je ne sais pas comment revenir sur la table de resultat qui regroupe uniquement par commande et mettre les valeurs sommées du code ci dessus...

j'ai compris qu'il faudra faire un LEFT JOIN mais je sais pas comment faire cela
15/11/2022, 18h07
al1_24

Citation:

Envoyé par awa123

j'ai compris qu'il faudra faire un LEFT JOIN mais je sais pas comment faire cela

Tout est là : Les jointures ;)

j'ai tenté la requete suivante mais je constate que les resultats sont incohérents

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT t1.num_commande
	,sum(t1.ligne)
	,count(DISTINCT t1.code_article)
	,count(DISTINCT t1.famille)
	,sum(t2.ligne)
	,count(DISTINCT t2.famille)
FROM Table_commande t1
LEFT JOIN Table_commande t2 ON t1.num_commande = t2.num_commande
WHERE t2.num_commande IN (
		SELECT t2.num_commande
		FROM Table_commande t2
		GROUP BY t2.num_commande
			,t2.famille
		HAVING sum(t2.ligne) > 1
		)
GROUP BY t1.num_commande

merci d'avance de votre aide

16/11/2022, 08h26
tatayo

Bonjour,
Tu as mis une restriction sur T2 dans la clause WHERE, ce qui change la jointure externe en jointure interne.
Il faut déplacer le In dans la jointure.

Tatayo.

je n'y arrive pas, lorsque je met le In dans la jointure y'a un pb

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT t1.num_commande
	,sum(t1.ligne)
	,count(DISTINCT t1.code_article)
	,count(DISTINCT t1.famille)
	,sum(t2.ligne)
	,count(DISTINCT t2.famille)
FROM Table_commande t1
LEFT JOIN Table_commande t2                       
WHERE t2.num_commande IN (
		SELECT t2.num_commande
		FROM Table_commande t2
		GROUP BY t2.num_commande
			,t2.famille
		HAVING sum(t2.ligne) > 1
		)
ON t1.num_commande = t2.num_commande
GROUP BY t1.num_commande

16/11/2022, 10h40
tatayo

"Il y a un problème :roll:".
Tout à fait. Ta jointure est mal écrite.

Tatayo.
16/11/2022, 10h50
escartefigue

En effet, le mot clef ON doit être positionné immédiatement après la jointure JOIN et donc avant la restriction WHERE.

De plus, mettre une restriction avec égalité sur une colonne de la table OUTER T2 remplace implicitement la jointure externe (OUTER JOIN) en jointure interne (INNER JOIN)

Ca dérive !
Essayez ainsi :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
with cte_commande_famille_groupe (num_commande, famille) as
(
  select num_commande, famille
    from Table_commande
group by num_commande, famille
  having sum(ligne) > 1
)
    select t1.num_commande
         , sum(t1.ligne)
         , count(distinct t1.code_article)
         , count(distinct t1.famille)
         , count(distinct t2.famille)
         , count(t2.famille)
      from Table_commande              as t1
 left join cte_commande_famille_groupe as t2  on t2.num_commande = t1.num_commande
                                             and t2.famille      = t1.famille
  group by t1.num_commande;

1 pièce(s) jointe(s)

Bonjour,

merci à tous de votre aide

mille mercis Waldar pour la proposition de code, je ne connaissais pas du tout les tables dérivées CTE, je trouve ça hyper puissant et pratique !

J'ai donc tenté de l'utiliser en faisant le test ci dessous mais je ne comprends pas le resultat de la "sum(t2.ligne)" qui devrait correspondre à la somme des lignes pour les familles qui sont > 1 ligne.
par exemple pour le colis1 j'attend le resultat 4 car nous avons 2 familles de 2 lignes chacune (2 lignes de la familleA + 2 lignes de la familleB=4)
pour le colis 4 j'attend le resultat de sum(t2.ligne) = 2 car dans le colis 4 il n'y a que familleD qui a > 1 ligne (en l'occurence la somme est de 2 lignes)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 WITH cte_commande_famille_groupe (numero_colis ,famille,ligne) AS ( SELECT numero_colis,famille, sum(ligne) FROM table_CTE GROUP BY numero_colis, famille HAVING sum(ligne) > 1 ) SELECT t1.numero_colis ,sum(t1.ligne) sum_ligne ,count(DISTINCT t1.article) distinct_art ,count(DISTINCT t1.famille) distinct_fam ,count(DISTINCT t2.famille) distinct_fam_t2 ,count(t2.ligne) count_ligne_t2 ,sum(t2.ligne) sum_ligne_t2 FROM table_CTE AS t1 LEFT JOIN cte_commande_famille_groupe AS t2 ON t2.numero_colis = t1.numero_colis AND t2.famille = t1.famille GROUP BY t1.numero_colis;
Pièce jointe 629274

merci de votre aide

21/11/2022, 14h50
Waldar

Parce que vous avez mal réappliqué la logique, je ne récupère pas la partie "sum ligne" dans la CTE de façon volontaire.
Suivez la préconisation initiale : count(t2.famille) et count(distinct t2.famille).

Edit : d'ailleurs vous les affichez bien, supprimez simplement le sum(t2.ligne). Regardez vos colonnes distinct_fam_t2 et count_ligne_t2 ils ont les chiffres que vous cherchez.