Problème de SUM/GROUP BY

**ALLODREN** · 27/05/2009, 10h59

AVIS AUX EXPERT SQL !

Cette requête ne me renvoi pas ttes les lignes ...

Si je retire mon SUM la requête me renvoi bien toutes les lignes ...
Si je mets dans mon " GROUP BY" ttes les critères, cela me renvoi toutes les lignes mais ne me calcule pas la somme.

Je reste désemparé.

Pourriez vous, s'il vous plaît, me guider ?

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
SELECT
  A.id_produit,
  A.id_fournisseur,
  A.ty_document,
  A.nu_document,
  A.qt_cmde_ini ,
  A.px_commande,
  A.ty_commande,
  (A.px_commande*A.qt_cmde_ini) AS Valorisation,
  A.id_user
 
/* Calcul de la somme*/
from(
SELECT
 
  k.id_produit,
  k.id_fournisseur,
  k.dt_document,
  k.qt_cmde_ini,
  k.ty_commande,
  k.id_societe,
  k.id_offre,
  k.ty_document,
  k.nu_document,
  k.nucdli,
  k.id_user,
  k.dt_livraison,
  k.dt_confirmation,
  sum(k.px_commande) as px_commande /* Calcul de la somme*/
 
   from(
       SELECT /* SOUS REQUETE 2. Extraction des données agrégées par CDA*/
       z.id_produit,
       z.id_fournisseur,
       z.dt_document,
       z.qt_cmde_ini,
       z.ty_commande,
       z.id_societe,
       z.id_offre,
       z.id_user,
       z.dt_livraison,
       z.dt_confirmation,
       z.px_commande,
 
                          CASE z.cde_ter /*Dans le cas de cde ter*/
                   WHEN 0 THEN z.ty_document /* Si cde ter = 0 ( quand ty_document <> CDA alors  on récupère ty_document*/
                   ELSE 'CDA' /* Sinon on récupère CDA*/
                   END AS ty_document, /*Tout ceci est dans ty_document*/
 
                   CASE z.cde_ter
                   WHEN 1 THEN NULL /* Si ty_document = CDA * alors on récupère rien */
                   ELSE z.nucdli /*Sinon on récupère z.nucdli*/
                   END AS nucdli, /*tout ceci est dans nucdli, z.px_commande, z.cde_ter*/
                    z.cde_ter,
 
                   CASE z.cde_ter
                   WHEN 1 THEN z.nucdli /* Si ty_document = CDA alors on écrit rien z.nucdli */
                   ELSE z.nu_document /* Sinon on récupère nu_document */
                   END AS nu_document  /* Tout ceci est dans nu_document */
 
 
           FROM  (SELECT *,
			   /* SOUS REQUETE 3. Agrégation des données dans le cas où nucdli = 				nu_commande */
                                CASE
                                WHEN EXISTS (SELECT 1 /* SOUS REQUETE 4. Extraction des données où nucdli = nu_commande  et ty_commande = CDA */
                                        FROM   tbl_ligneachat h
                                        WHERE  h.id_produit = q.id_produit
                                        AND q.nucdli = h.nu_document
                                        AND h.ty_document = 'CDA') /* FIN SOUS REQUETE 4 */
                                THEN 1
                                ELSE 0
                                END AS cde_ter
 
                           FROM   tbl_ligneachat Q
           )AS z /* FIN SOUS REQUETE 3 */
 
   )AS k
 
Group by
       k.id_produit,
       k.qt_cmde_ini,
       k.ty_commande,
       k.nu_document,
       k.nucdli,
       k.id_societe,
       k.id_offre
)as A

**Waldar** · 27/05/2009, 11h11

La requête que vous avez postée ne correspond à aucun de vos choix :
"Si je retire mon SUM la requête me renvoi bien toutes les lignes"
"Si je mets dans mon " GROUP BY" ttes les critères, cela me renvoi toutes les lignes mais ne me calcule pas la somme"

Sans rentrer dans les détails je l'écrirai de la façon suivante :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
SELECT
  A.id_produit,
  A.id_fournisseur,
  A.ty_document,
  A.nu_document,
  A.qt_cmde_ini ,
  A.px_commande,
  A.ty_commande,
  (A.px_commande*A.qt_cmde_ini) AS Valorisation,
  A.id_user
 
/* Calcul de la somme*/
FROM(
SELECT
 
  k.id_produit,
  k.id_fournisseur,
  k.dt_document,
  k.qt_cmde_ini,
  k.ty_commande,
  k.id_societe,
  k.id_offre,
  k.ty_document,
  k.nu_document,
  k.nucdli,
  k.id_user,
  k.dt_livraison,
  k.dt_confirmation,
  sum(k.px_commande) AS px_commande /* Calcul de la somme*/
 
   FROM(
       SELECT /* SOUS REQUETE 2. Extraction des données agrégées par CDA*/
       z.id_produit,
       z.id_fournisseur,
       z.dt_document,
       z.qt_cmde_ini,
       z.ty_commande,
       z.id_societe,
       z.id_offre,
       z.id_user,
       z.dt_livraison,
       z.dt_confirmation,
       z.px_commande,
 
                          CASE z.cde_ter /*Dans le cas de cde ter*/
                   WHEN 0 THEN z.ty_document /* Si cde ter = 0 ( quand ty_document <> CDA alors  on récupère ty_document*/
                   ELSE 'CDA' /* Sinon on récupère CDA*/
                   END AS ty_document, /*Tout ceci est dans ty_document*/
 
                   CASE z.cde_ter
                   WHEN 1 THEN NULL /* Si ty_document = CDA * alors on récupère rien */
                   ELSE z.nucdli /*Sinon on récupère z.nucdli*/
                   END AS nucdli, /*tout ceci est dans nucdli, z.px_commande, z.cde_ter*/
                    z.cde_ter,
 
                   CASE z.cde_ter
                   WHEN 1 THEN z.nucdli /* Si ty_document = CDA alors on écrit rien z.nucdli */
                   ELSE z.nu_document /* Sinon on récupère nu_document */
                   END AS nu_document  /* Tout ceci est dans nu_document */
 
 
           FROM  (SELECT *,
			   /* SOUS REQUETE 3. Agrégation des données dans le cas où nucdli = 				nu_commande */
                                CASE
                                WHEN EXISTS (SELECT 1 /* SOUS REQUETE 4. Extraction des données où nucdli = nu_commande  et ty_commande = CDA */
                                        FROM   tbl_ligneachat h
                                        WHERE  h.id_produit = q.id_produit
                                        AND q.nucdli = h.nu_document
                                        AND h.ty_document = 'CDA') /* FIN SOUS REQUETE 4 */
                                THEN 1
                                ELSE 0
                                END AS cde_ter
 
                           FROM   tbl_ligneachat Q
           )AS z /* FIN SOUS REQUETE 3 */
 
   )AS k
 
GROUP BY
  k.id_produit,
  k.id_fournisseur,
  k.dt_document,
  k.qt_cmde_ini,
  k.ty_commande,
  k.id_societe,
  k.id_offre,
  k.ty_document,
  k.nu_document,
  k.nucdli,
  k.id_user,
  k.dt_livraison,
  k.dt_confirmation
)AS A

**ALLODREN** · 27/05/2009, 13h10

WAlDaR :

Si je retire mon SUM la requête me renvoi bien toutes les lignes ...
Si je mets dans mon " GROUP BY" ttes les critères, cela me renvoi toutes les lignes mais ne me calcule pas la somme

Ceci n'est pas mon choix mais un constat

Le GROUP BY que vous avez fait est logique :

___On groupe toujours par au moins tous les éléments de la sélection___

Mais dans ce cas : cela ne me calcule pas correctement mon besoin qui est :

Addition des valeurs "px_commande" pour toutes les lignes de ma table qui ont : nucdli = nu_document.

On affiche la somme dans le px_commande de la ligne où se trouve le "nu_document" identique.

Et on ne retient plus la ligne où se trouve le "nucdli" lié.

Ex :

Ma table actuelle est

id_prod | ty_document | nu_document | nucdli | px_commande

A | CDA | 160 | |2
A| CDD | 161 | 160 | 2
A | CDA | 162 | | 2

Je souhaite l' extraire tel que :

A|CDA|160| |4
A|CDA|162||2

Mon premier résultat est l'addition entre la première et seconde ligne qui ont nucdli = nu_document.

---> RÉCAPITULONS:

Dans un premier temps, cette requête regroupe les "nucdli"
(n° commandes liées) et les "nu_document" ( n ° commande ) qui ont les mêmes identifiants ( 2 colonnes dans une même table qui ont les mêmes identifiants).

Dans un second temps, elle est censée additionner les "px_commande" ( le coût des commandes) des regroupements ( au total, il y a 8 regroupement sur 40 664 lignes ).

Étant donnée que ma requête fusionne les "nucdli" et les "nu_document" qui ont les mêmes identifiants, et qu'il y en a 8, la requête devrait me retourner 40 656 lignes.
Or ma requête fait bien la somme et effectue bien la fusion mais ne me renvoi que 40 546 lignes soit une centaine en moins !

Est ce plus clair ! ?

**Waldar** · 27/05/2009, 14h41

Oui c'est plus clair.
Dans le select et le group by, remplacez

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
  k.nu_document,
  k.nucdli,

Par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

    coalesce(k.nucdli, k.nu_document) as nu_document

Sans le as dans le group by.

Dites-moi si ça fonctionne mieux.

**ALLODREN** · 27/05/2009, 15h01

Cette modification n'a malheureusement rien changé ...

**Waldar** · 27/05/2009, 15h29

J'ai supposé que le champ nucdli était null lorsqu'il n'y a rien de renseigné, mais ce n'est peut-être pas le cas.

Y a-t'il un espace ou une chaîne vide ?

**ALLODREN** · 27/05/2009, 15h49

Attention :
Il y a une chaîne vide = ""

Ce n'est pas = null

**Waldar** · 27/05/2009, 15h52

Dans ce cas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CASE WHEN k.nucdli = '' THEN k.nu_document ELSE k.nucdli END

**ALLODREN** · 27/05/2009, 16h07

Aie ...

Cette modification m'enlève des lignes si je mets le "when case" dans le "select" et le "group by".
Si je le mets seulement dans le Group by, cela ne change rien.

L'idée est pourtant intéressante

**Waldar** · 27/05/2009, 17h08

C'est normal que vous perdiez des lignes.
Regardez votre propre exemple, vous passez de trois lignes à deux.

**ALLODREN** · 27/05/2009, 17h42

OUI !

Je suis pleinement d'accord avec vous mais je ne dois perdre que 8 lignes (puisqu'il y a 8 ligne où nucdli = nu_document) et non pas 101 !

Lorsque j'ôte cette somme de la requête : J'ai bien toutes mes lignes - 8.

Donc j'ai identifié le calcul comme source de ce problème cornélien.

**Waldar** · 27/05/2009, 17h49

Remplacez votre somme par un count(*) et faites une analyse des groupes, là le problème se situe je pense sur la qualité de vos données.

**ALLODREN** · 28/05/2009, 17h20

Après analyse des groupes

Je me suis rendu compte que lorsque je fais mon calcul, toutes les nu_document qui étaient en double, pour un produit, n'étaient pas pris en compte qu'une seule fois ....

EX :

id_prod | nu_doc

100|160
100|160
100|150

La requête ne me retourne que {100;160 et 100;150}

Voila mes cent lignes manquantes.

Comment les rapatriés ? Ca c'est la grande question ...

**Waldar** · 28/05/2009, 17h34

C'est le principe de l'aggrégat sum !
En fait vous ne voulez additionner les prix uniquement pour les lignes où nucdli = nu_document ?

**ALLODREN** · 28/05/2009, 17h37

EXACTEMENT !

Supprimer la nucdli et mettre la somme dans le nu_document correspondant.

**Waldar** · 28/05/2009, 18h00

Il faut faire quelque chose de ce genre :

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
SELECT
  k.id_produit,
  k.id_fournisseur,
  k.dt_document,
  k.qt_cmde_ini,
  k.ty_commande,
  k.id_societe,
  k.id_offre,
  k.ty_document,
  k.nu_document,
  k.id_user,
  k.dt_livraison,
  k.dt_confirmation,
  k.px_commande + coalesce(k2.px_commande, 0) AS px_commande
 
   FROM 
         (...) AS k
         LEFT OUTER JOIN (...) AS k2
           ON k2.nucdli <> ''
          AND k2.nucdli = k.nu_document
WHERE
    k.nucdli = ''

Il y a surement moyen d'améliorer tout ça, mais l'idée c'est de faire une jointure entre ceux qui n'ont pas de nucdli et ceux qui en ont un.

**ALLODREN** · 29/05/2009, 13h32

Ton code est astucieux. La requête me renvoie toutes les lignes.

Cependant, cela ne m'additionne pas les lignes qui ont un nucdli=nu_document

Toujours le même problème.

J'ai essayé de tourner le code ci-après de différentes façons, je présente la version la plus cohérente.

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
 
SELECT
  k.id_produit,
  k.id_fournisseur,
  k.dt_document,
  k.qt_cmde_ini,
  k.ty_commande,
  k.id_societe,
  k.id_offre,
  k.ty_document,
  k.nu_document,
  k.id_user,
  k.dt_livraison,
  k.dt_confirmation,
  k.px_commande + coalesce(k3.px_commande,0) AS px_commande
 
from
  tbl_ligneachat k
 
           LEFT OUTER JOIN (
 
SELECT
  k2.id_produit,
  k2.id_fournisseur,
  k2.dt_document,
  k2.qt_cmde_ini,
  k2.ty_commande,
  k2.id_societe,
  k2.id_offre,
  k2.ty_document,
  k2.nu_document,
  k2.id_user,
  k2.dt_livraison,
  k2.dt_confirmation,
  k2.px_commande,
  k2.nucdli
 
   FROM tbl_ligneachat K2) as K3
 
 
 
         ON  K3.nucdli is not null and k3.nucdli=k.nu_document
 
where K.nucdli is null
 
         order by id_produit

**ALLODREN** · 30/06/2009, 14h09

Salut à tous !

Je reviens sur ce problème que je n'ai pas encore résolu....

Retour en arrière :

Quand Id_prod=id_prod et nucdli=nu_doc et ty_doc = "CDA" avec ty_doc=(CDC,CDD,CDT) alors SUM(px_com).
Ensuite j'afface CDC,CDT,CDD lié.

Pour expliciter ma requête, voici le jeu de données suivant :

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
+-|id_prod |ty_doc|nu_doc|nucdli|px_com|id_prod|ty_doc |nu_doc | nucdli  |px_com 
+---------+--------+--------+--------+--------+---------+--------+--------+-----
| A      | BE   |13604 | NULL |    4 | E     | CDC   |  13661 |  13604 |      3 |                                    |
| A      | BE   |13604 | NULL |    4 | B     | CDC   |  13361 |  13604 |      2 |                                    |
| A      | BE   |13604 | NULL |    4 | E     | CDT   |  13360 |  13604 |      1 |                                    |
| C      | CDA  |13604 | NULL |    3 | E     | CDT   |  13360 |  13604 |      1 |                                    |        |
| C      | CDA  |13604 | NULL |    3 | E     | CDC   |  13661 |  13604 |      3 |                                    |
| C      | CDA  |13604 | NULL |    3 | B     | CDC   |  13361 |  13604 |      2 |                                    |
| D      | CDA  |13604 | NULL |    3 | E     | CDC   |  13661 |  13604 |      3 |                                    |
| D      | CDA  |13604 | NULL |    3 | B     | CDC   |  13361 |  13604 |      2 |                                    |
| D      | CDA  |13604 | NULL |    3 | E     | CDT   |  13360 |  13604 |      1 |                                    |
| E      | FA   |  444 | NULL |    1 | NULL  | NULL  |   NULL |   NULL |   NULL |                                    |
| E      | BE   |  555 | NULL |    1 | NULL  | NULL  |   NULL |   NULL |   NULL |                                    |
| E      | CDA  |13604 | NULL |    2 | E     | CDC   |  13661 |  13604 |      3 |                                    |
| E      | CDA  |13604 | NULL |    2 | B     | CDC   |  13361 |  13604 |      2 |                                    |
| E      | CDA  |13604 | NULL |    2 | E     | CDT   |  13360 |  13604 |      1 |                                    |
+---------+--------+--------+--------+--------+---------+--------+--------+----

Voici le résultat attendu :

+

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
-|id_prod |ty_doc|nu_doc|nucdli|px_com|id_prod|ty_doc |nu_doc | nucdli  |px_com 
+---------+--------+--------+--------+--------+---------+--------+--------+-----
| A      | BE   |13604 | NULL |    4 | E     | CDC   |  13661 |  13604 |      3 |                                    |
| A      | BE   |13604 | NULL |    4 | B     | CDC   |  13361 |  13604 |      2 |                                    |
| A      | BE   |13604 | NULL |    4 | E     | CDT   |  13360 |  13604 |      1 |                                    |
| C      | CDA  |13604 | NULL |    3 | E     | CDT   |  13360 |  13604 |      1 |                                    |        |
| C      | CDA  |13604 | NULL |    3 | E     | CDC   |  13661 |  13604 |      3 |                                    |
| C      | CDA  |13604 | NULL |    3 | B     | CDC   |  13361 |  13604 |      2 |                                    |
| D      | CDA  |13604 | NULL |    3 | E     | CDC   |  13661 |  13604 |      3 |                                    |
| D      | CDA  |13604 | NULL |    3 | B     | CDC   |  13361 |  13604 |      2 |                                    |
| D      | CDA  |13604 | NULL |    3 | E     | CDT   |  13360 |  13604 |      1 |                                    |
| E      | FA   |  444 | NULL |    1 | NULL  | NULL  |   NULL |   NULL |   NULL |                                    |
| E      | BE   |  555 | NULL |    1 | NULL  | NULL  |   NULL |   NULL |   NULL |                                    |
| E      | CDA  |13604 | NULL |    6 |       |       |        |        |       |                                    |
| E      | CDA  |13604 | NULL |    6 | B     | CDC   |  13361 |  13604 |      2 |                                    |
| E      | CDA  |13604 | NULL |    6 |       |       |        |        |       |                                    |
+---------+--------+--------+--------+--------+---------+--------+--------+-----

Enfin voici ma requête : Elle n'additionne pas les px_commande et ne renvoie pas toutes les lignes...

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
SELECT /* Selection of request (paramèters et order) */
  A.id_produit,
  A.ty_document,
  A.nu_document,
  A.dt_document,
   A.px_commande,
  A.ty_commande
 
FROM
(SELECT /* SOUS REQUETE 1. Calcul (px_commande) */
 
  Q.id_produit,
  Q.ty_commande,
  Q.ty_document,
  Q.nu_document,
  Q.nucdli,
  Sum(q.px_commande) AS px_commande /* Calcul de la somme*/
    FROM
      ( SELECT /* SOUS REQUETE 2. Extraction agregate date with CDA*/
       z.id_produit,
       z.ty_commande,
                          CASE z.cde_ter 
                   WHEN 0 THEN z.ty_document 
                   ELSE 'CDA' 
                   END AS ty_document, 
                   CASE z.cde_ter
                   WHEN 1 THEN NULL 
                   ELSE z.nucdli 
                   END AS nucdli, 
                   z.px_commande,
                   z.cde_ter,
                   CASE z.cde_ter
                   WHEN 1 THEN z.nucdli 
                   ELSE z.nu_document 
                   END AS nu_document  
 
 	   /* SOUS REQUETE 3. Agregation of data when nucdli =    	nu_commande */
                                CASE
                                WHEN EXISTS (SELECT 1 /* SOUS REQUETE 4. Extraction data where nucdli = nu_commande  et ty_commande = CDA */
                                        FROM   tbl_ligneachat h
                                        WHERE  h.id_produit = k.id_produit
                                        AND k.nucdli = h.nu_document
                                        AND h.ty_document = 'CDA') /* FIN SOUS REQUETE 4 */
                                THEN 1
                                ELSE 0
                                END AS cde_ter
                           FROM   tbl_ligneachat k
                            )AS z /* FIN SOUS REQUETE 3 */
        ) AS Q /* FIN SOUS REQUETE 2 */
     GROUP BY /* Groupement effectué pour le calcul de px_commande */
       q.id_produit,
       q.ty_commande,
       q.nu_document,
       q.nucdli
 
)AS A

Problème de SUM/GROUP BY

Langage SQL

Discussions similaires

Partager

Partager