IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Requête CTE Common Table Expression


Sujet :

Développement SQL Server

  1. #1
    Membre habitué
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2012
    Messages
    352
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Octobre 2012
    Messages : 352
    Points : 182
    Points
    182
    Par défaut Requête CTE Common Table Expression
    Bonjour,

    avec la requete suivante, je cherche à afficher la somme de la qté commandé par colis
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 et le
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    Images attachées Images attachées  

  2. #2
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 934
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 934
    Points : 4 347
    Points
    4 347
    Par défaut
    vous demandez "count()" et vous l'obtenez... si vous voulez la somme c'est "sum()" qu'il faut utiliser...

  3. #3
    Membre habitué
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2012
    Messages
    352
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Octobre 2012
    Messages : 352
    Points : 182
    Points
    182
    Par défaut
    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)

  4. #4
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    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.

  5. #5
    Membre habitué
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2012
    Messages
    352
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Octobre 2012
    Messages : 352
    Points : 182
    Points
    182
    Par défaut
    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

  6. #6
    Expert éminent sénior
    Homme Profil pro
    Responsable Données
    Inscrit en
    Janvier 2009
    Messages
    5 188
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable Données

    Informations forums :
    Inscription : Janvier 2009
    Messages : 5 188
    Points : 12 744
    Points
    12 744
    Par défaut
    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.

  7. #7
    Membre habitué
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2012
    Messages
    352
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Octobre 2012
    Messages : 352
    Points : 182
    Points
    182
    Par défaut
    Bonjour Tatayo,

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

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    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.

  8. #8
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 084
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 084
    Points : 38 377
    Points
    38 377
    Billets dans le blog
    9
    Par défaut
    Bonjour awa123
    Citation Envoyé par awa123 Voir le message
    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

  9. #9
    Membre habitué
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2012
    Messages
    352
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Octobre 2012
    Messages : 352
    Points : 182
    Points
    182
    Par défaut
    bonjour,

    je souhaiterai vous montrer un autre exemple très simple

    ci joint en image ma table et

    voici mon script :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    Images attachées Images attachées  

  10. #10
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 084
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 084
    Points : 38 377
    Points
    38 377
    Billets dans le blog
    9
    Par défaut
    Ce n'est pourtant pas bien compliqué

    Avec votre jeu d'essai :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    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 SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    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.

  11. #11
    Membre habitué
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2012
    Messages
    352
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Octobre 2012
    Messages : 352
    Points : 182
    Points
    182
    Par défaut
    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

  12. #12
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 934
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 934
    Points : 4 347
    Points
    4 347
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  13. #13
    Membre habitué
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2012
    Messages
    352
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Octobre 2012
    Messages : 352
    Points : 182
    Points
    182
    Par défaut
    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
    Images attachées Images attachées  

  14. #14
    Expert éminent sénior
    Homme Profil pro
    Responsable Données
    Inscrit en
    Janvier 2009
    Messages
    5 188
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable Données

    Informations forums :
    Inscription : Janvier 2009
    Messages : 5 188
    Points : 12 744
    Points
    12 744
    Par défaut
    Bonjour,
    Il faut peut être ajouter un alias à la sous requête.

    Tatayo

  15. #15
    Membre habitué
    Homme Profil pro
    Étudiant
    Inscrit en
    Octobre 2012
    Messages
    352
    Détails du profil
    Informations personnelles :
    Sexe : Homme

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Octobre 2012
    Messages : 352
    Points : 182
    Points
    182
    Par défaut
    Mille mercis !
    c'est trop cool quand ca marche !!!

  16. #16
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Septembre 2006
    Messages
    2 934
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : Septembre 2006
    Messages : 2 934
    Points : 4 347
    Points
    4 347
    Par défaut
    Citation Envoyé par tatayo Voir le message
    Il faut peu être ajouter un alias à la sous requête.
    Juste : j'ai pas testé en SQLServer... c'était censé être du "standard"...

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. PHP, MsSql et Common Table Expressions
    Par saisiman dans le forum PHP & Base de données
    Réponses: 5
    Dernier message: 17/03/2016, 13h47
  2. CTE (Common Table Expression) sous MYSQL
    Par patic dans le forum Requêtes
    Réponses: 7
    Dernier message: 04/09/2013, 14h42
  3. Réponses: 1
    Dernier message: 22/01/2009, 17h48
  4. Réponses: 4
    Dernier message: 26/06/2008, 13h54
  5. Requête sur 2 tables et surtout novice...
    Par kibodio dans le forum Langage SQL
    Réponses: 13
    Dernier message: 03/03/2005, 16h45

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo