Requête avec deux SUM sur deux tables, l'une multiplie l'addition de l'autre si plus qu'une entrée

Version imprimable

Bonjour,

J'ai un petit souci avec une requete qui contient deux "SUM".
Dès qu'il y a deux (trois,...) montants dans produit_prix ; MontantPaiement est multiplié par deux (trois,...) et dès qu'il y a deux (trois,...) montants dans payement_montant; MontantProduit est multiplié par deux (trois,...).
Code:

1 2 3 4 5 6 7 8 SELECT studenten.student_pk, studenten.student_naam, studenten.student_voornaam, studenten.student_stam, SUM(produit_prix) AS MontantProduit, SUM(payement_montant) AS MontantPaiement FROM studenten LEFT JOIN eco_factures ON eco_factures.facture_stam=studenten.student_stam INNER JOIN eco_produits ON eco_produits.produit_pk=eco_factures.facture_produit LEFT JOIN eco_payements ON eco_payements.payement_stam=studenten.student_stam GROUP BY studenten.student_pk, studenten.student_naam, studenten.student_stam
Comment pourrai-je remédier à cela ?

Merci d'avance pour votre aide,

Sub

Il faut effectuer l'une des sommes dans une table dérivée :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT  std.student_pk
    ,   std.student_naam
    ,   std.student_voornaam
    ,   std.student_stam
    ,   mnt.MontantProduit
    ,   SUM(pmt.payement_montant)   AS MontantPaiement
FROM    studenten     std
    LEFT JOIN
        (   SELECT  fct.facture_stam
                ,   SUM(prd.produit_prix)   AS MontantProduit
            FROM    eco_factures    fct 
                INNER JOIN 
                    eco_produits    prd 
                    ON  prd.produit_pk   = fct.facture_produit
            GROUP BY fct.facture_stam
        )   mnt 
        ON  mnt.facture_stam = std.student_stam
    LEFT JOIN 
        eco_payements   pmt 
        ON  pmt.payement_stam = std.student_stam
GROUP BY  std.student_pk
    ,     std.student_naam
    ,     std.student_stam

30/09/2016, 10h29
escartefigue

bonjour,

Faites vos calculs (sum et groupage) dans une sous requête corrélée et une jointure inner entre votre requête principale et cette sous-requête ;)
EDIT : grillé par AL1_24 :)
Avec un autre SGBD que MySQL une CTE aurait permis de simplifier la rédaction de la requete en invitant les imbricatinos

Citation:

Envoyé par al1_24

Il faut effectuer l'une des sommes dans une table dérivée :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT  std.student_pk
    ,   std.student_naam
    ,   std.student_voornaam
    ,   std.student_stam
    ,   mnt.MontantProduit
    ,   SUM(pmt.payement_montant)   AS MontantPaiement
FROM    studenten     std
    LEFT JOIN
        (   SELECT  fct.facture_stam
                ,   SUM(prd.produit_prix)   AS MontantProduit
            FROM    eco_factures    fct 
                INNER JOIN 
                    eco_produits    prd 
                    ON  prd.produit_pk   = fct.facture_produit
            GROUP BY fct.facture_stam
        )   mnt 
        ON  mnt.facture_stam = std.student_stam
    LEFT JOIN 
        eco_payements   pmt 
        ON  pmt.payement_stam = std.student_stam
GROUP BY  std.student_pk
    ,     std.student_naam
    ,     std.student_stam

Merci al1_24 !

Et pour y ajouter que la somme "pmt.payement_montant" ne doit être faite que si payement_campagne = '1'
et que la somme "prd.produit_prix" ne doit être faite que si facture_campagne = '1'

J'ai essayer avec WHERE mais ...

30/09/2016, 12h03
al1_24

Il suffit d'ajouter ces restrictions aux bons endroits : dans la sous-requête pour la seconde, dans la condition de jointure pour la première.

Oui, c'est ce que j'ai fait mais dans ce cas-là il ne m'affiche pas les student_pk qui n'ont pas d'entrée dans eco_payements et ceux-là devrait aussi être affiché afin de voir qui n'a rien payé.

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
 
SELECT  std.student_pk
    ,   std.student_naam
    ,   std.student_voornaam
    ,   std.student_stam
    ,   mnt.MontantProduit
    ,   SUM(pmt.payement_montant)   AS MontantPaiement
FROM    studenten     std
    LEFT JOIN
        (   SELECT  fct.facture_stam
                ,   SUM(prd.produit_prix)   AS MontantProduit
            FROM    eco_factures    fct 
                INNER JOIN 
                    eco_produits    prd 
                    ON  prd.produit_pk   = fct.facture_produit WHERE facture_campagne = '1' 
            GROUP BY fct.facture_stam
        )   mnt 
        ON  mnt.facture_stam = std.student_stam
    LEFT JOIN 
        eco_payements   pmt 
        ON  pmt.payement_stam = std.student_stam WHERE payement_campagne = '1'
GROUP BY  std.student_pk
    ,     std.student_naam
    ,     std.student_stam

J'ai dit "dans la condition de jointure", pas sur l'intégralité de la requête... ;)
Code:

1 2 3 4 LEFT JOIN eco_payements pmt ON pmt.payement_stam = std.student_stam AND pmt.payement_campagne = '1'

Merci Al1_24

Cette fois il tient bien compte pour les deux calculs de montants de la campagne 1 mais il m'affiche encore ceux qui n'ont rien dans la table eco_factures comme MontantProduit = NULL

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
 
SELECT  std.student_pk
    ,   std.student_naam
    ,   std.student_voornaam
    ,   std.student_stam
    ,   mnt.MontantProduit
    ,   SUM(pmt.payement_montant)   AS MontantPaiement
FROM    studenten     std
    LEFT JOIN
        (   SELECT  fct.facture_stam
                ,   SUM(prd.produit_prix)   AS MontantProduit
            FROM    eco_factures    fct 
                INNER JOIN 
                    eco_produits    prd 
                    ON  prd.produit_pk   = fct.facture_produit AND fct.facture_campagne = '1' 
            GROUP BY fct.facture_stam
        )   mnt 
        ON  mnt.facture_stam = std.student_stam
    LEFT JOIN 
        eco_payements   pmt 
        ON  pmt.payement_stam = std.student_stam AND pmt.payement_campagne = '1'
GROUP BY  std.student_pk
    ,     std.student_naam
    ,     std.student_stam

Comment je peux faire pour que ceux qui ont NULL dans la colonne MontantProduit ne s'affichent pas ?

Merci en tout cas :-)

Sub

Salut à tous.

Je propose une autre solution, suite à la remarque d'Escartefigue que je trouve pertinente (+1), qui n'a pas été reprise dans ce sujet.
Elle consiste à faire la somme de vos montants, à partir des sous-requêtes corrélées, comme ci-après :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT  std.student_pk,
        std.student_naam,
        std.student_voornaam,
        std.student_stam,
 
       (select      SUM(prd.produit_prix)
              from  eco_factures  AS FCT
        inner join  eco_produits  AS PRD
                on  prd.produit_pk    = fct.facture_produit
               and  fct.facture_stam  = std.student_stam
          group by  fct.facture_stam
       ) AS MontantProduit,
 
       (select      SUM(pmt.payement_montant)
              from  eco_payements AS PMT
             where  pmt.payement_stam = std.student_stam
          group by  pmt.payement_stam
       ) AS MontantPaiement,
 
  FROM  studenten AS STD 
 WHERE  std.payement_campagne = '1';

Je n'ai pas testé, donc je ne sais pas si ce que je propose est exsangue de toutes erreurs.

@+

Citation:
Envoyé par Artemus24
Salut à tous.

Je propose une autre solution, suite à la remarque d'Escartefigue que je trouve pertinente (+1), qui n'a pas été reprise dans ce sujet.
Elle consiste à faire la somme de vos montants, à partir des sous-requêtes corrélées, comme ci-après :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SELECT std.student_pk, std.student_naam, std.student_voornaam, std.student_stam, (select SUM(prd.produit_prix) from eco_factures AS FCT inner join eco_produits AS PRD on prd.produit_pk = fct.facture_produit and fct.facture_stam = std.student_stam group by fct.facture_stam ) AS MontantProduit, (select SUM(pmt.payement_montant) from eco_payements AS PMT where pmt.payement_stam = std.student_stam group by pmt.payement_stam ) AS MontantPaiement, FROM studenten AS STD WHERE std.payement_campagne = '1';
Je n'ai pas testé, donc je ne sais pas si ce que je propose est exsangue de toutes erreurs.

@+
Cela retourne :

Code:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM studenten AS STD WHERE std.payement_campagne = '1' LIMIT 0, 30' at line 20

30/09/2016, 15h52
Artemus24

Salut subran.

Je suis fâche avec les virgules. Il y en a une de trop là : ") AS MontantPaiement,".
Désolé pour cette erreur. :oops:

@+
30/09/2016, 17h26
subran
Citation:

Envoyé par Artemus24

Salut subran.

Je suis fâche avec les virgules. Il y en a une de trop là : ") AS MontantPaiement,".
Désolé pour cette erreur. :oops:

@+

Sans la virgule il affiche l'erreur
Code:

1 2 #1054 - Unknown column 'std.student_pk' in 'field list'

Bonjour,

J'essaye encore de ne pas afficher les lignes qui retroune NULL pour la colonne MontantProduit. J'ai essayé avec AND SUM(prd.produit_prix) IS NOT NULL mais cela ne fonctionne pas.
Et alors j'aimerais afficher juste une ligne où student_stam = '1234' (1234 étant l'identifiant de l'utilisateur pour lequel on fait les calculs)

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
 
SELECT  std.student_pk
    ,   std.student_naam
    ,   std.student_voornaam
    ,   std.student_stam
    ,   mnt.MontantProduit
    ,   COALESCE(SUM(pmt.payement_montant),0)  AS MontantPaiement
FROM    studenten     std 
    LEFT JOIN
        (   SELECT  fct.facture_stam
                ,   COALESCE(SUM(prd.produit_prix),0)   AS MontantProduit
            FROM    eco_factures    fct 
                INNER JOIN 
                    eco_produits    prd 
                    ON  prd.produit_pk   = fct.facture_produit AND fct.facture_campagne = '1' 
            GROUP BY fct.facture_stam, fct.facture_campagne ='1'
        )   mnt 
        ON  mnt.facture_stam = std.student_stam 
    LEFT JOIN 
        eco_payements   pmt 
        ON  pmt.payement_stam = std.student_stam AND pmt.payement_campagne = '1'
GROUP BY  std.student_pk
    ,     std.student_naam
    ,     std.student_stam

Merci encore pour toutes les réponses,

Sub

30/09/2016, 19h06
Artemus24

Salut subran.

Si tu as des NULL qui te poses des problèmes, il suffit de faire ceci :

Code:

SUM(COALESCE(pmt.payement_montant),0) AS MontantPaiement

et non l'inverse :

Code:

COALESCE(SUM(pmt.payement_montant),0) AS MontantPaiement

car tu fais d'abord la somme, puis ensuite tu vérifies si tu as NULL, donc dans la somme, pour venir mettre zéro.
Je te rappelle que : NULL + 33 = NULL.
Si dès le départ, tu converties ton NULL en zéro, cela te donne : 0 + 33 = 33.

@+

Re-Salut

Voici l'exemple de ce que j'avance et là, ça fonctionne :

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
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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `pere`
--------------
 
--------------
CREATE TABLE `pere`
( `id`   integer unsigned NOT NULL auto_increment primary key,
  `lib`  varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pere` (`lib`) values
  ('un'),('deux'),('trois')
--------------
 
--------------
select * from pere
--------------
 
+----+-------+
| id | lib   |
+----+-------+
|  1 | un    |
|  2 | deux  |
|  3 | trois |
+----+-------+
--------------
DROP TABLE IF EXISTS `fils1`
--------------
 
--------------
CREATE TABLE `fils1`
( `id`       integer unsigned NOT NULL auto_increment primary key,
  `pere_id`  integer unsigned NOT NULL,
  `montant`  decimal(15,2)        NULL,
  CONSTRAINT `FK_FILS1_PERE` FOREIGN KEY (`pere_id`) REFERENCES `pere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `fils1` (`pere_id`,`montant`) values
  (1,10.00),(1,25.00),(1, 5.00),(2,14.00),(2,26.00),(3,28.00),(3,12.00)
--------------
 
--------------
select * from fils1
--------------
 
+----+---------+---------+
| id | pere_id | montant |
+----+---------+---------+
|  1 |       1 |   10.00 |
|  2 |       1 |   25.00 |
|  3 |       1 |    5.00 |
|  4 |       2 |   14.00 |
|  5 |       2 |   26.00 |
|  6 |       3 |   28.00 |
|  7 |       3 |   12.00 |
+----+---------+---------+
--------------
DROP TABLE IF EXISTS `fils2`
--------------
 
--------------
CREATE TABLE `fils2`
( `id`       integer unsigned NOT NULL auto_increment primary key,
  `pere_id`  integer unsigned NOT NULL,
  `montant`  decimal(15,2)        NULL,
  CONSTRAINT `FK_FILS2_PERE` FOREIGN KEY (`pere_id`) REFERENCES `pere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `fils2` (`pere_id`,`montant`) values
  (1,17.00),(1,NULL),(2,22.00),(2,18.00),(3,23.00),(3,8.00),(3,9.00)
--------------
 
--------------
select * from fils2
--------------
 
+----+---------+---------+
| id | pere_id | montant |
+----+---------+---------+
|  1 |       1 |   17.00 |
|  2 |       1 |    NULL |
|  3 |       2 |   22.00 |
|  4 |       2 |   18.00 |
|  5 |       3 |   23.00 |
|  6 |       3 |    8.00 |
|  7 |       3 |    9.00 |
+----+---------+---------+
--------------
select      t1.lib, sum(coalesce(t2.montant,0)) as total_fils1
      from  pere  as t1
 
inner join  fils1 as t2
        on  t2.pere_id = t1.id
 
  group by  t1.lib
  order by  t1.id
--------------
 
+-------+-------------+
| lib   | total_fils1 |
+-------+-------------+
| un    |       40.00 |
| deux  |       40.00 |
| trois |       40.00 |
+-------+-------------+
--------------
select      t1.lib, sum(coalesce(t3.montant,0)) as total_fils2
      from  pere  as t1
 
inner join  fils2 as t3
        on  t3.pere_id = t1.id
 
  group by  t1.lib
  order by  t1.id
--------------
 
+-------+-------------+
| lib   | total_fils2 |
+-------+-------------+
| un    |       17.00 |
| deux  |       40.00 |
| trois |       40.00 |
+-------+-------------+
--------------
select      t1.lib,
           (select sum(coalesce(t2.montant,0)) from fils1 as t2 where t2.pere_id = t1.id group by t2.pere_id) as total_fils1,
           (select sum(coalesce(t3.montant,0)) from fils2 as t3 where t3.pere_id = t1.id group by t3.pere_id) as total_fils2
      from  pere  as t1
  order by  t1.id
--------------
 
+-------+-------------+-------------+
| lib   | total_fils1 | total_fils2 |
+-------+-------------+-------------+
| un    |       40.00 |       17.00 |
| deux  |       40.00 |       40.00 |
| trois |       40.00 |       40.00 |
+-------+-------------+-------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Les NULL se transforment bien en 0 mais la requête m'affiche tous les student même ceux qui n'ont pas de lien avec eco_factures (via facture_stam) du coup j'ai des lignes en trop et pour ces lignes j'ai NULL pour la colonne MontantProduit. Ce qui est normal mais j'essaye de n'avoir que les student_stam = facture_stam

La requête ci-dessous fonctionne bien mais il y a trop de ligne. J'essaye d'y ajouter student_stam = facture_stam pour n'avoir que les élèves qui ont des factures (et j'essaye aussi de n'avoir qu'une ligne pour un étudiant (student_stam) au choix.
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 SELECT std.student_pk , std.student_naam , std.student_voornaam , std.student_stam , mnt.MontantProduit , COALESCE(SUM(pmt.payement_montant),0) AS MontantPaiement FROM studenten std LEFT JOIN ( SELECT fct.facture_stam , COALESCE(SUM(prd.produit_prix),0) AS MontantProduit FROM eco_factures fct INNER JOIN eco_produits prd ON prd.produit_pk = fct.facture_produit AND fct.facture_campagne = '1' GROUP BY fct.facture_stam, fct.facture_campagne ='1' ) mnt ON mnt.facture_stam = std.student_stam LEFT JOIN eco_payements pmt ON pmt.payement_stam = std.student_stam AND pmt.payement_campagne = '1' GROUP BY std.student_pk , std.student_naam , std.student_stam

J'y suis presque. la requete ne m'affiche plus que les lignes qui ont une entrée dans la table eco_factures mais je n'arrive pas à ne faire apparaitre que les lignes qui ont facture_campagne ='1'

Code:

1
2
3
4
5
6
7
 
SELECT student.student_pk , student.student_naam , student.student_voornaam , student.student_stam , student.MontantProduit , student.MontantPaiement,student.Differance FROM
( SELECT std.student_pk , std.student_naam , std.student_voornaam , std.student_stam , mnt.MontantProduit ,COALESCE(SUM(pmt.payement_montant),0) AS MontantPaiement,(MontantProduit - COALESCE(SUM(pmt.payement_montant),0) ) AS  Differance
FROM studenten std LEFT JOIN (  SELECT fct.facture_stam , COALESCE(SUM(prd.produit_prix),0) AS MontantProduit  
  FROM eco_factures fct   INNER JOIN 
    eco_produits prd ON prd.produit_pk = fct.facture_produit AND fct.facture_campagne = '1' GROUP BY fct.facture_stam, fct.facture_campagne ='1' )mnt  ON mnt.facture_stam = std.student_stam LEFT JOIN eco_payements pmt ON pmt.payement_stam = std.student_stam AND pmt.payement_campagne = '1' GROUP BY std.student_pk , std.student_naam , std.student_stam )
    student JOIN eco_factures ecoF  ON  ecoF.facture_stam = student.student_stam   GROUP BY  student.student_pk

Dès que j'ai trouvé, je poste la solution. Au cas où qqun aurait une illumination ... je continue à chercher.

Bonne soirée,

Sub

03/10/2016, 14h03
skuatamad

Vous faîtes une jointure externe entre studenten et mnt, donc vous récupérez des MontantProduit null, c'est ce que vous demandez...
Une jointure interne semble mieux répondre à votre besoin.

Citation:

Envoyé par Artemus24

Je te rappelle que : NULL + 33 = NULL.

Vrai pour l'opérateur + mais faux pour la fonction d'agrégation SUM :
Variations sur NULL, ou SUM(X+Y) <> SUM(X) + SUM(Y) ?