Discussion :

[socaw]

Bonjour,

Initialement, j'ai un peu travaillé dans Access où je faisait mes requêtes au moyen du QBE. Actuellement, je me mets à utiliser MySQL et j'aimerais réutiliser une série de requêtes que j'utilisais dans Access.

Au départ de trois tables (schéma en pièce jointe), je voulais obtenir des données agrégées. Il y a une table Client, reprenant les clients potentiels. Il y une table des dépenses ordinaires de ces clients et une table des dépenses extra-ordinaires de ces clients.
Le résultat que je voulais obtenir était le suivant : avoir la dépense totale par client (en sachant que la dépense totale est la somme des dépenses ordinnaires et extra-ordinaires).

Pour y arriver je passais par trois requêtes :
Requete1

Code :

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

1
2
3
SELECT Client.NumClient, Client.Age, Sum(Depense_Base.Montant) AS Dep_Base
FROM Client LEFT JOIN Depense_Base ON Client.NumClient = Depense_Base.NumClient
GROUP BY Client.NumClient

Requete2

Code :

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

1
2
3
SELECT Client.NumClient, Client.Age, Sum(Depense_Extra.Montant) AS Dep_Extra
FROM Client LEFT JOIN Depense_Extra ON Client.NumClient = Depense_Extra.NumClient
GROUP BY Client.NumClient

Requete finale

Code :

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

1
2
3
SELECT Client.NumClient, Client.Age, [requete1.Dep_Base]+[requete2.Dep_Extra] AS Dep_Tot
FROM (Client LEFT JOIN requete1 ON Client.NumClient = requete1.NumClient)
LEFT JOIN requete2 ON Client.NumClient = requete2.NumClient

J'ai donc deux questions sur cet exemple :

Premièrement,
Comment faire pour utiliser cette troisième requête dans MySQL ? Puisque dans Access, la troisième requête a été élaborée au moyen de deux autres requêtes?

Deuxièmement,
(c'est certainement lié au premier point) N'y a-t-il pas moyen de "regrouper" ces requêtes en une seule étape ? J'ai regardé la documentation relative aux sous-requêtes mais je ne trouve pas (ou pas encore) un cas qui pourrait m'aider.

[islamov2000]

si tu veux regrouper toutes en une ;voici la requete

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
SELECT Client.NumClient, Client.Age, requete1.Dep_Base+requete2.Dep_Extra AS Dep_Tot
FROM 
 
(Client LEFT JOIN 
 
(SELECT Client.NumClient, Client.Age, Sum(Depense_Base.Montant) AS Dep_Base
FROM Client LEFT JOIN Depense_Base 
ON Client.NumClient = Depense_Base.NumClient
GROUP BY Client.Age,Client.NumClient
) AS requete1 
 
ON Client.NumClient = requete1.NumClient
 
) 
 
LEFT JOIN 
 
(SELECT Client.NumClient, Client.Age, Sum(Depense_Extra.Montant) AS Dep_Extra
FROM Client LEFT JOIN Depense_Extra 
ON Client.NumClient = Depense_Extra.NumClient
GROUP BY Client.Age,Client.NumClient
) AS requete2 
 
ON Client.NumClient = requete2.NumClient;

[socaw]

J'ai essayé la requête regroupée, mais elle ne sort aucun résultat pour les dépenses totales (champ Dep_Tot).

[CinePhil]

Essaie comme ça :

Code :

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

1
2
3
4
5
6
SELECT Client.NumClient, Client.Age, 
  SUM(Depense_Base.Montant + Depense_Extra.Montant) AS Depense
FROM Client 
LEFT JOIN Depense_Base ON Client.NumClient = Depense_Base.NumClient
LEFT JOIN Depense_Extra ON Client.NumClient = Depense_Extra.NumClient
GROUP BY Client.NumClient, Client.Age

Si tu as un résultat NULL, il faut COALESCE pour éviter l'addition de valeurs nulles :

Code :

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

1
2
3
4
5
6
SELECT Client.NumClient, Client.Age, 
  SUM(COALESCE(Depense_Base.Montant, 0) + COALESCE(Depense_Extra.Montant, 0)) AS Depense
FROM Client 
LEFT JOIN Depense_Base ON Client.NumClient = Depense_Base.NumClient
LEFT JOIN Depense_Extra ON Client.NumClient = Depense_Extra.NumClient
GROUP BY Client.NumClient, Client.Age

[pacmann]

Salut !

Attention Cinephile, si je ne me trompe pas, ta requête est fausse dès que le lien n'est pas exactement 1-1 sur l'une des tables détail : et cela risque d'être fortement probable, sans quoi on n'aurait pas besoin de GROUP BY...)

[EDIT] Ah oui : la première réponse semble elle bien meilleure à première vue, tente d'y intégrer le COALESCE

[socaw]

Salut !

[EDIT] Ah oui : la première réponse semble elle bien meilleure à première vue, tente d'y intégrer le COALESCE

Je vais tester, je vous tiens au courant.
Merci pour vos conseils !

[islamov2000]

sur quel SGBD travailles tu?

[islamov2000]

je voix que la la deuxieme solution de CinePhil est plus correcte ; car elle traite le cas de (valeur + null);

voici version pour MSACCESS

Code sql :

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

1
2
3
4
5
SELECT Client.NumClient, Client.Age, Sum(IIf([Depense_Base].[Montant] Is Not Null,[Depense_Base].[Montant],0)+IIf([Depense_Extra].[Montant] Is Not Null,[Depense_Extra].[Montant],0)) AS Depense

FROM (Client LEFT JOIN Depense_Base ON Client.NumClient = Depense_Base.Numclient) 
LEFT JOIN Depense_Extra ON Client.NumClient = Depense_Extra.numclient
GROUP BY Client.NumClient, Client.Age;

version ORACLE

Code :

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

1
2
3
4
5
SELECT Client.NumClient, Client.Age,
Sum(NVL(Depense_Base.Montant ,0)+NVL(Depense_Extra.Montant,0)) AS Depense
FROM (Client 
LEFT JOIN Depense_Base ON Client.NumClient = Depense_Base.Numclient) LEFT JOIN Depense_Extra ON Client.NumClient = Depense_Extra.numclient
GROUP BY Client.NumClient, Client.Age

[socaw]

sur quel SGBD travailles tu?

Je travaillais avec MS Access, mais actuellement je migre vers MySQL.

Ta solution et celle de CinePhil ne donne pas un résultat correct (si vous avez la possibilité de regarder la capture d'écran attachée, vous verrez la différence entre la requete3 qui est mon ancienne manière de travailler et la requete_boussafi qui sur-estime les dépenses totales).

[pacmann]

Boussafi, c'est pas parce que le nom du monsieur est écrit en vert qu'il faut recopier ses conneries, surtout que TA première requête ne commettait pas la même erreur !!!

Suppose pour le client 1 que :
depense extra:
numclient / montant
1 100
1 50

depense base
1 20
1 10

La jointure entre depense extra et depense base donne (je pense que vous savez comment marchent les jointures) :
1 100 1 20
1 100 1 10
1 50 1 20
1 50 1 10

=> c'est pour ça que vous en avez beaucoup trop !

Donc, commencez par calculer le cumul séparément sur chaque table de dépenses avec des GROUP BY séparés, puis faites la jointure !

Au passage Boussafi, comme tu as l'air plein de bonne volonté :
Utilise COALESCE qui fait la même chose NVL, sauf que c'est universel et commun à tous les SGBD

[islamov2000]

socaw,j'ai exploité des données que t'as posté et j'ai eu même resulat que la tienne en regoupant les 3 requetes.

la fonction COALESCE n'a pas marché sous MSACCESS, tu peux l'utiliser si ça ira

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
SELECT Client.NumClient, Client.Age, 
Sum(IIf([Depense_Base].[Montant] Is Not Null,[Depense_Base].[Montant],0)+IIf([Depense_Extra].[Montant] Is Not Null,[Depense_Extra].[Montant],0)) AS Depense
 
FROM (
           (SELECT Sum(Depense_Base.Montant) AS Montant, Depense_Base.Numclient as  Numclient
             FROM Depense_Base
            GROUP BY Depense_Base.Numclient) AS Depense_Base 
 
RIGHT JOIN Client 
 
ON Depense_Base.Numclient = Client.NumClient
) 
 
LEFT JOIN 
 
          (SELECT Sum(Depense_extra.Montant) AS Montant, Depense_extra.Numclient as Numclient
           FROM Depense_extra
           GROUP BY Depense_extra.Numclient) AS Depense_Extra 
 
 
ON Client.NumClient = Depense_Extra.Numclient
 
 
 
GROUP BY Client.NumClient, Client.Age;

[socaw]

Merci pour vos réponses.
Je suis sur la bonne voie !
Mes anciennes requêtes en plusieurs étapes dans Access se font maintenant en une seule étape dans MySQL.