Requête CTE Common Table Expression

Bonjour,

avec la requete suivante, je cherche à afficher la somme de la qté commandé par colis

Code:

---

count(t1.qte_uvc) sum_qty_t1

---

qui fonctionne mais aussi la somme de la qté du colis uniquement des Ref_colori qui ont + d'une ligne. d'où le

Code:

---

HAVING sum(ligne) > 1

---

et le

Code:

---

count(t2.qte_uvc) sum_qty_t2

---

sauf que ce n'est pas le resultat constaté, par exemple j'ai affiché le détails d'un colis (cf image jointe) , au lieu de m'afficher la qté 18, ça m'affiche 11 qui correspond au nb de lignes du colis pour lesquelle les famille distinctes ont + d'une ligne mais pas la qté totale de ces ref_colori du même colis.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

with cte_commande_famille_groupe (annee,mois,jour, numcolis,ligne, ref_colori,qte_uvc,sku) as ( select annee,mois,jour,numcolis,sum(ligne), ref_colori , sum(qte_uvc), count(distinct sku) from prep_global_backup GROUP BY jour ,numcolis ,ref_colori HAVING sum(ligne) > 1 ) select t1.jour ,t1.numcolis ,count(t1.qte_uvc) sum_qty_t1 ,count(t2.qte_uvc) sum_qty_t2   from prep_global_backup as t1 left join cte_commande_famille_groupe as t2 on t2.jour = t1.jour and t2.numcolis = t1.numcolis and t2.Ref_Colori = t1.Ref_Colori GROUP BY t1.jour ,t1.numcolis

---

merci beaucoup d'avance

vous demandez "count()" et vous l'obtenez... si vous voulez la somme c'est "sum()" qu'il faut utiliser...

merci JeitEmgie de votre aide

sur l'exemple que j'ai donné (le détail du colis), je ne trouve pas le résultat qui affiche la somme des quantité pour lesquelles les familles distinctes (dans cet exemple on constate 3 familles différentes, illustré par les 3 couleurs) ont + de 1 ligne

en l'occurence je cherche à obtenir le resultat 18 (dans cet exemple)

Vous n'êtes vraiment pas très rigoureux.
D'une part je ne pense pas que ce sujet nécessitait un nouveau fil de discussion, mais surtout vous égrenez les informations et les besoins au compte-goutte.

Quelles sont vos données en entrée et que voulez-vous en sortie ?
Et arrêtez SVP de modifier à chaque fois les colonnes présentées - surtout quand elles sont utiles à la logique.

Bonjour Waldar,

Tout d'abord merci beaucoup de votre aide !
Effectivement, j'avais hésité à remettre cette question sur mon fil précédent, mais j'avais déja marqué en résolu et c'est aprés que j'ai réalisé que j'avais oublié cette question. donc dsl dsl...

Je cherche simplement à sommer la colonne "qte_uvc" par ref_colori mais si et seulement si on a + de 1 ligne par "ref_colori", d'où le HAVING sum(ligne) > 1" dans la requete CTE.
par exemple, dans l'image que j'ai posté, on voit 4 lignes (donc strictement supérieur à > 1 lignes) pour la ref_colori en jaune (1+2+1+1=5), ensuite on voit 3 lignes pour la ref_colori en vert (1+3+3=7), et 4 lignes en bleu (2+2+1+1=6); le resultat que attendu est donc 5+7+6=18.

Hélas, avec count(t2.qte_uvc) sum_qty_t2 je n'ai pas le resultat attendu...

J'espère que c'est plus clair sinon dites le moi, j'illustrerai davantage

Bonjour,
Tu cherches les lignes qui sont présentes plus d'une fois, et tu mets comme critères "having sum(ligne) > 1".
Que signifie "sum" en anglais ?

Tu veux faire la somme des quantité, et tu utilises "count(t2.qte_uvc) ".
Que signifie "count" en anglais ? Une piste, tu mets comme alias "sum_qty_t2" pour la somme.

Tatayo.

Bonjour Tatayo,

oui je mets having sum(ligne) > 1
car je rappelle que c'est dans un group by

Code:

---

1 2 3 4 5

GROUP BY jour ,numcolis ,ref_colori HAVING sum(ligne) > 1

---

sachant que j'ai une colonne "ligne" avec la valeur 1 tout le temps, le having sum(ligne) permet donc de savoir si ma "ref_colori" de mon group by a + de 1 ligne ou pas.

Bonjour awa123

Citation:

---

Envoyé par awa123

Je cherche simplement à sommer la colonne "qte_uvc" par ref_colori
[...]
Hélas, avec count(t2.qte_uvc) sum_qty_t2 je n'ai pas le resultat attendu...

---

Ce qu'ont tenté de vous expliquer JeitEmgie et Tatayo, visiblement en vain, c'est que pour faire une somme, il ne faut pas utiliser COUNT(), mais SUM(), tout simplement !
Il faut donc utiliser la fonction SUM() dans la clause SELECT et dans la clause GROUP BY ... HAVING

Par ailleurs, pensez à encadrer votre code avec les balises CODE (bouton # de la barre d'icônes), ça facilite la lecture.
J'ai modifié vos précédents messages pour ajouter ces balises ;)

bonjour,

je souhaiterai vous montrer un autre exemple très simple

ci joint en image ma table et

voici mon script :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

with cte_commande_famille (numero_colis,ligne,famille, qte) as ( select numero_colis,sum(ligne), famille , sum(qte) from bdd GROUP BY numero_colis, famille HAVING sum(ligne) > 1 ) select t1.numero_colis ,count( distinct t1.famille) distinct_famille ,sum(t1.ligne) sum_ligne ,sum(t1.qte) sum_qte ,count(distinct t2.famille) distinct_famille_t2 ,sum(t2.ligne) sum_ligne_t2 ,sum(t2.qte) sum_qte_t2 from bdd as t1 left join cte_commande_famille as t2 on t2.numero_colis = t1.numero_colis   GROUP BY t1.numero_colis

---

et voici le resultat que j'ai en image jointe

versus le resultat que je souhaite avoir en image jointe


la question est : pourquoi je n'ai pas les reusltats attendus, par exemple pour le colis 1 sum_ligne devrait être 4, sum_qte devrait être 8 , sum_ligne_t2 devrait etre 4 et sum_qte_t2 devrait etre 8.

merci

Ce n'est pourtant pas bien compliqué

Avec votre jeu d'essai :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

with tab(colis, famille, ligne, qte, article) as (select 'C1', 'FA', 1, 2, 'a' union all select 'C1', 'FA', 1, 2, 'b' union all select 'C1', 'FB', 1, 2, 'c' union all select 'C1', 'FB', 1, 2, 'd' union all select 'C2', 'FC', 1, 2, 'e' union all select 'C3', 'FC', 1, 2, 'f' union all select 'C4', 'FD', 1, 2, 'g' union all select 'C4', 'FD', 1, 2, 'h' union all select 'C4', 'FE', 1, 2, 'i' )

---

Cette requête toute simple :

Code:

---

1 2 3 4 5 6

select colis , count(distinct famille) as nb_fam , sum(ligne) as sum_ligne , sum(qte) as sum_qte from tab group by colis

---

Donne le résultat attendu :

colis	nb_fam	sum_ligne	sum_qte
C1	2	4	8
C2	1	1	2
C3	1	1	2
C4	2	3	6

Tous les éléments permettant d'y arriver étaient pourtant donnés dans les réponses qui précèdent.

ci-dessous une petite video youtube dans laquelle j'exprime ce que souhaite avoir comme résultat (en espérant que je sois clair)


merci

Code:

---

1 2 3 4 5 6 7 8 9 10 11

select colis, count(distinct famille) as distinct_famille, sum(ligne) as sum_ligne, sum(qte) as sum_quantite, count(distinct case when n_familles > 1 then famille end) as distinct_famille_t2, sum(case when n_familles > 1 then ligne else null end) as sum_ligne_t2, sum(case when n_familles > 1 then qte else null end) as sum_qte_t2 from ( select colis, famille, ligne, qte, count(famille) over(partition by colis, famille) as n_familles from bdd ) group by colis ;

---

Code:

---

1 2 3 4 5

C1 2 4 8 2 4 8 C2 1 1 2 0 C3 1 1 2 0 C4 2 3 6 1 2 4

---

c'est parfait c'est exactement le resultat que je veux !

mais j'ai une petite erreur lorsque j'execute votre script "Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'GROUP'.
"

c'est surement un détail mais je ne trouve pas l'erreur

Bonjour,
Il faut peut être ajouter un alias à la sous requête.

Tatayo

Mille mercis !
c'est trop cool quand ca marche !!!

Citation:

---

Envoyé par tatayo

Il faut peu être ajouter un alias à la sous requête.

---

Juste : j'ai pas testé en SQLServer... c'était censé être du "standard"...