[SQL] SUM de SUM() [Résolu]

[philodido]

Bonjour,

Je cherche depuis un bon moment comment faire une requête.

Voici l'énoncé de mon problème :

J'ai 2 tables qui se présentent comme suit :

Table des objectifs :

nego_id | agence_id | objectif
1		1	52000
2		1	48560
3 		1      24561
4		2	49787
5		2	45211
6		3	56988
...

Table des transactions :

tra_nego_id | tra_agence_id | tra_honoraires1 | tra_honoraires2 | tra_honoraires3 | tra_honoraires4 
1		   1		          1240		2458	   3254			1548
2		   1		          4896		7854	   2584			7985	
3		   1			  1231		4567	   7899			1569
4		   2			  1548		7896	   4521			7965
5		   2			  2548		7895	   1468			4684
6		   3			  4521		7845	   5456			7458
...

Dans un premier temps je voulais ressortir un résultat comme suit :

nego_id | objectif | somme_honoraires 
1	   52000	   8500
...

J'ai donc fait une requête qui se présente comme suit (pour l'agence 1) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT O.nego_id, objectif 
         	,SUM(T1.tra_honoraires1) AS 'somme 1'
            ,SUM(T2.tra_honoraires2) AS 'somme 2'
            ,SUM(T3.tra_honoraires3) AS 'somme 3'
            ,SUM(T4.tra_honoraires4) AS 'somme 4'           
FROM     Objectifs O
LEFT JOIN Transactions T1 				ON O.nego_id = T1.tra_honoraires1
LEFT JOIN Transactions T2 				ON O.nego_id = T2.tra_honoraires2
LEFT JOIN Transactions T3 				ON O.nego_id = T3.tra_honoraires3
LEFT JOIN Transactions T4 				ON O.nego_id = T4.tra_honoraires4                    
WHERE     O.agence_id = 1
GROUP BY  O.nego_id,	O.objectif

Puis j’additionne en php les honoraires de chaque négociateur pour avoir le résultat voulu.

Première question : Comment avoir le résultat directement à la sortie de la requête, autrement dit : Comment faire la somme des sommes par nego dans la requête ? (j'ai bien tenté de faire un SUM des SUMs mais ça n'a pas l'air de fonctionner)

Deuxième question : Comment obtenir le même résultat par agence ? :

agence_id | objectif | somme_honoraires 
1	   125121	   47085
...

Merci de votre aide.

[punkoff]

bonjour,

si la relation entre vos tables objectifs et transactions est de type :
objectifs 0,1 ------- 0,1 Transactions

alors le group by est inutile (vu l'exemple ça doit être ça).

Donc en partant de ce postulat :

Code :

1
2
3
4
5
 
SELECT obj.nego_id, obj.objectif,
tra_honoraires1 + tra_honoraires2 + tra_honoraires3 + tra_honoraires4 AS somme_honoraires 
FROM objectifs obj
LEFT OUTER JOIN transactions trans ON trans.tra_nego_id = obj.nego_id AND trans.tra_agence_id = obj.agence_id

edit : enfin vu vos clauses de jointures je doute que votre requête actuelle marche ou alors vous avez mal décrit vos tables...

Concernant le résultat par agence :

Code :

1
2
3
4
5
6
 
SELECT obj.agence_id, sum(obj.objectif) AS somme_agence,
sum(tra_honoraires1 + tra_honoraires2 + tra_honoraires3 + tra_honoraires4) AS somme_honoraires 
FROM objectifs obj
LEFT OUTER JOIN transactions trans ON trans.tra_agence_id = obj.agence_id
GROUP BY obj.agence_id

[philodido]

Oui effectivement, désolé , en voulant simplifier je me suis trompé dans l'énoncé :

Il faut rajouter les colonnes suivantes à la table Transactions :

tra_nego_honoraires1 | tra_nego_honoraires2 | tra_nego_honoraires3 | tra_nego_honoraires4
(celles-ci désignant les negos qui touchent respectivement les honoraires : tra_honoraires1, tra_honoraires2, tra_honoraires3, tra_honoraires4)

La problématique est que le nego qui passe la transaction (tra_nego) n'est pas le seul à toucher des honoraires (les colonnes ci-dessus).

Donc, ma requête est en fait de cette forme :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT O.nego_id, objectif 
         	,SUM(T1.tra_honoraires1) AS 'somme 1'
            ,SUM(T2.tra_honoraires2) AS 'somme 2'
            ,SUM(T3.tra_honoraires3) AS 'somme 3'
            ,SUM(T4.tra_honoraires4) AS 'somme 4'           
FROM     Objectifs O
LEFT JOIN Transactions T1 				ON O.nego_id = T1.tra_nego_honoraires1
LEFT JOIN Transactions T2 				ON O.nego_id = T2.tra_nego_honoraires2
LEFT JOIN Transactions T3 				ON O.nego_id = T3.tra_nego_honoraires3
LEFT JOIN Transactions T4 				ON O.nego_id = T4.tra_nego_honoraires4                    
WHERE     O.agence_id = 1
GROUP BY  O.nego_id,	O.objectif

[punkoff]

Bonjour,

Je ne comprend pas, veuillez décrire vos table et les ralations entre elle correctement.

Merci.

exemple :
objectif (nego_id, agence_id , objectif, ..)

[philodido]

Oui je comprend, désolé si je me suis mal exprimé mais ça n'est pas facile à expliquer. Je vais tenter de préciser :

Objectifs (nego_id, agence_id , objectif)

Transactions(tra_nego_id,tra_agence_id,tra_nego_honoraires1,tra_nego_honoraires2,tra_nego_honoraires3,tra_nego_honoraires4,tra_honoraires1,
tra_honoraires2,tra_honoraires3,tra_honoraires4)

Explications des colonnes :

Pour la table Objectifs :

nego_id = l'identifiant du nego
agence_id = son agence
objectif = son objectif d'honoraires

Pour la table Transactions :

tra_nego_id = l'identifiant du nego qui à saisi la transaction
tra_agence_id = son agence
tra_nego_honoraires1 = le nego bénéficiaire de l'honoraire tra_honoraires1 (c'est forcément le nego qui à saisi la transaction)
tra_nego_honoraires2 = le nego bénéficiaire de l'honoraire tra_honoraires2
tra_nego_honoraires3 = le nego bénéficiaire de l'honoraire tra_honoraires3
tra_nego_honoraires4 = le nego bénéficiaire de l'honoraire tra_honoraires4

Ces tables étaient existantes je ne les ai pas créées moi même.
Bien entendu elles sont simplifiées pour l'exemple (Transactions > 100 champs).

[punkoff]

Ok !

Dommage que la table soit mal modélisée.
donc oui votre requête de départ était sur la bonne piste.

Est-ce que ceci fonctionne chez vous ?

Code :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT O.nego_id, sum(objectif) ,
SUM(COALESCE(T1.tra_honoraires1, 0)) +  SUM(COALESCE(T2.tra_honoraires2, 0)) 
+ SUM(COALESCE(T3.tra_honoraires3, 0)) + SUM(COALESCE(T4.tra_honoraires4, 0)) AS 'somme_objectifs'           
FROM     Objectifs O
LEFT JOIN Transactions T1 				ON O.nego_id = T1.tra_nego_honoraires1
LEFT JOIN Transactions T2 				ON O.nego_id = T2.tra_nego_honoraires2
LEFT JOIN Transactions T3 				ON O.nego_id = T3.tra_nego_honoraires3
LEFT JOIN Transactions T4 				ON O.nego_id = T4.tra_nego_honoraires4                    
WHERE     O.agence_id = 1
GROUP BY  O.nego_id

les COALESCE sont peut-être inutile, selon votre SGBD.

Concernant le problème du mode agence, je penses que la requête que je vous ai proposée doit être bonne.


edit : Si vous avez beaucoup de donnée, une consolidation en amont des honnoraires par négociant serai peut être intéresssante

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH tmp (nego_id, honnoraire) AS (
SELECT tra_nego_honoraires1, sum(tra_honoraires1)
FROM transactions
GROUP BY tra_nego_honoraires1
union ALL 
SELECT tra_nego_honoraires2, sum(tra_honoraires2)
FROM transactions
GROUP BY tra_nego_honoraires2
...)
 
SELECT O.nego_id, sum(objectif) ,sum(honnoraire)
FROM objectifs O
LEFT OUTER JOIN tmp trans ON o.nego_id = trans.nego_id 
GROUP BY O.nego_id

[philodido]

je suis en train de tester, j'ai un problème technique indépendant du sujet, je te réponds dès que possible...

up (désolé pour le timing - j'avais de gros soucis ): Ok pour la solution par nego. J'ai pas testé la solution consolidée, mais ça me va déjà très bien comme ça.

Néanmoins je n'obtiens pas la bonne somme d'honoraires avec la requête par agence.
Par exemple pour l'agence 5 avec la requête par nego j'obtiens 2315 (sur toute l'agence - ce qui est le bon résultat) alors je j'obtiens 17550 sur l'agence 5 avec la requête par agence.
Je l'impression qu'il fait l'addition de tous les honoraires de chaque ligne ayant 5 pour tra_agence_id... la solution à l'air très complexe.

Si tu n'as pas la solution je peux me débrouillé en faisant les calculs par agence en php. Mais d'ores et déjà : un ÉNORME MERCI

[punkoff]

Envoyé par philodido

Néanmoins je n'obtiens pas la bonne somme d'honoraires avec la requête par agence.
Par exemple pour l'agence 5 avec la requête par nego j'obtiens 2315 (sur toute l'agence - ce qui est le bon résultat) alors je j'obtiens 17550 sur l'agence 5 avec la requête par agence.
Je l'impression qu'il fait l'addition de tous les honoraires de chaque ligne ayant 5 pour tra_agence_id... la solution à l'air très complexe.

Oui, la requete fait une aggrégation par agence de tous les honnoraires.

Je ne comprend pas bien le problème ?

Explicitez votre besoin car là... je ne peux pas deviner ce qu'il vous faut !

Edit : en fait je commence a avoir un doute sur la requete de base des négo.

En effet on applique un filtre sur l'agence qui n'est pas répercuté au niveau des "LEFT OUTER JOIN". Donc je penses que le résultat ne doit pas être si juste que ca.

Je transformerai bien les requetes de cette manière :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT O.nego_id, sum(objectif) ,
SUM(COALESCE(T1.tra_honoraires1, 0)) +  SUM(COALESCE(T2.tra_honoraires2, 0)) 
+ SUM(COALESCE(T3.tra_honoraires3, 0)) + SUM(COALESCE(T4.tra_honoraires4, 0)) AS 'somme_objectifs'           
FROM     Objectifs O
LEFT JOIN Transactions T1 				ON O.nego_id = T1.tra_nego_honoraires1 AND o.agence_id = T1.tra_agence_id
LEFT JOIN Transactions T2 				ON O.nego_id = T2.tra_nego_honoraires2 AND o.agence_id = T2.tra_agence_id
LEFT JOIN Transactions T3 				ON O.nego_id = T3.tra_nego_honoraires3 AND o.agence_id = T3.tra_agence_id
LEFT JOIN Transactions T4 				ON O.nego_id = T4.tra_nego_honoraires4 AND o.agence_id = T4.tra_agence_id
WHERE     O.agence_id = 1
GROUP BY  O.nego_id

et

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH tmp (nego_id, agence_id, honnoraire) AS (
SELECT tra_nego_honoraires1, tra_agence_id, sum(tra_honoraires1)
FROM transactions
GROUP BY tra_nego_honoraires1, tra_agence_id
union ALL 
SELECT tra_nego_honoraires2, tra_agence_id, sum(tra_honoraires2)
FROM transactions
GROUP BY tra_nego_honoraires2, tra_agence_id
...)
 
SELECT O.nego_id, sum(objectif) ,sum(honnoraire)
FROM objectifs O
LEFT OUTER JOIN tmp trans ON o.nego_id = trans.nego_id AND o.agence_id = trans.agence_id
GROUP BY O.nego_id

[philodido]

Envoyé par punkoff

Edit : en fait je commence a avoir un doute sur la requete de base des négo.

En tout cas d'après les test que j'ai fait on tombe sur les bons résultats.
Maintenant, comme j'ai testé sur des tables de test avec seulement 9 transactions, car la vrai table à été vidée (c'était en parti cela mon problème ) je ne peux pas garantir que ça fonctionne à 100%. Mais je vais essayer avec ta nouvelle requête.

Concernant la requête par agence, je cherche c'est tout """simplement""" à avoir un résultat par agence c'est à dire l'addition de tous les honoraires de tous les négos de chaque agence.

[punkoff]

hmm .. laissez moi comprendre ...

A quoi correspond tra_agence_id dans la table transaction ?

Ensuite est-ce qu'un nego peut appartenir à différente agence ?
Est-ce qu'un négo peut avoir des objectifs dans différentes agences ?

Comment sait-on qu'un négo appartient à une agence ?

au niveau de la table transaction, pour honnorraire1 à 4 comment savoir à quelle agence sont-ils rattachés ? A celle spécifié par tra_agence_id ?

[philodido]

Envoyé par punkoff

hmm .. laissez moi comprendre ...

A quoi correspond tra_agence_id dans la table transaction ?

Ensuite est-ce qu'un nego peut appartenir à différente agence ?
Est-ce qu'un négo peut avoir des objectifs dans différentes agences ?

Comment sait-on qu'un négo appartient à une agence ?

au niveau de la table transaction, pour honnorraire1 à 4 comment savoir à quelle agence sont-ils rattachés ? A celle spécifié par tra_agence_id ?

tra_agence_id correspond à l'agence du nego qui a saisi la transaction.

Ensuite est-ce qu'un nego peut appartenir à différente agence ? : non
Est-ce qu'un négo peut avoir des objectifs dans différentes agences ? : non

Comment sait-on qu'un négo appartient à une agence ? on peut le savoir soit en utilisant la table objectifs soit en utilisant la table transactions, indifféremment.

au niveau de la table transaction, pour honnorraire1 à 4 comment savoir à quelle agence sont-ils rattachés ? A celle spécifié par tra_agence_id ? non puisque tra_agence_id est l'agence du nego qui à saisir. Il fait regarder le nego bénéficiaire de l'honoraire (ex tra_nego_honraires1 pour tra_honoraires1) pour déterminer son agence.

[punkoff]

ok, donc oui vous avez raison les requêtes du post #6 sont correctes.

Et du coup la requête par agence va couler de soruce :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT O.agence_id, sum(objectif) ,
SUM(COALESCE(T1.tra_honoraires1, 0)) +  SUM(COALESCE(T2.tra_honoraires2, 0)) 
+ SUM(COALESCE(T3.tra_honoraires3, 0)) + SUM(COALESCE(T4.tra_honoraires4, 0)) AS 'somme_objectifs'           
FROM     Objectifs O
LEFT JOIN Transactions T1 				ON O.nego_id = T1.tra_nego_honoraires1
LEFT JOIN Transactions T2 				ON O.nego_id = T2.tra_nego_honoraires2
LEFT JOIN Transactions T3 				ON O.nego_id = T3.tra_nego_honoraires3
LEFT JOIN Transactions T4 				ON O.nego_id = T4.tra_nego_honoraires4                    
--WHERE     O.agence_id = 1
GROUP BY  O.agence_id

[philodido]

C'est ok pour la nouvelle requête nego et pour la version consolidée aussi

C'est ok pour la requête agence, par contre en version consolidée j'arrive pas à lui faire afficher la colonne agence_id

Mais de toute façon c'est déjà super comme ça. Encore merci du temps que tu as passé pour m'aider.

Si tu as encore un peu de temps, aurais l’obligeance de m'expliquer la requête nego version non consolidée, par exemple que fait exactement la fonction coalesce() dans ce cas précis ?

[punkoff]

elle prend le 1er résultat non null de la liste.
Vu qu'on travaille sur des left outer join, ceci étant dit je ne sais si c'est util de le rajouter avec votre sgbd.

[philodido]

Mon sgbd est ms sql. D'ailleurs j'utilise un driver ODBC pour me connecté à partir d'un serveur LAMP et j'ai souvent des problèmes divers à cause d'ODBC.
Est-ce que tu aurais une autre méthode de connexion à me conseiller ?
Est-ce que PDO fonctionne bien avec ms sql, car j'ai vu dans la doc de php que le driver était encore expérimental ?

Merci pour l'explication.

[punkoff]

Désolé je ne connais pas plus que ca ms sql, ceci étant dit tu as une section MS SQL, ils sauront t'aiguiller.

[Waldar]

Les colonnes tra_nego_honoraires sont-elles nécessaires ?

Votre premier besoin :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  SELECT obj.nego_id, obj.objectif
       , SUM(coalesce(trs.tra_honoraires1, 0)
           + coalesce(trs.tra_honoraires2, 0)
           + coalesce(trs.tra_honoraires3, 0)
           + coalesce(trs.tra_honoraires4, 0)) AS Honoraires
    FROM Objectifs obj
         LEFT OUTER JOIN Transactions trs
           ON trs.tra_nego_id = obj.nego_id
   WHERE obj.agence_id = 1
GROUP BY obj.nego_id, obj.objectif;
 
   NEGO_ID   OBJECTIF HONORAIRES
---------- ---------- ----------
         1      52000       8500
         2      48560      23319
         3      24561      15266

Votre second besoin :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  SELECT obj.agence_id 
       , SUM(obj.objectif) AS objectif_agence
       , SUM(coalesce(trs.tra_honoraires1, 0)
           + coalesce(trs.tra_honoraires2, 0)
           + coalesce(trs.tra_honoraires3, 0)
           + coalesce(trs.tra_honoraires4, 0)) AS Honoraires
    FROM Objectifs obj
         LEFT OUTER JOIN Transactions trs
           ON trs.tra_nego_id = obj.nego_id
GROUP BY obj.agence_id;
 
 AGENCE_ID OBJECTIF_AGENCE HONORAIRES
---------- --------------- ----------
         1          125121      47085
         2           94998      38525
         3           56988      25280

[philodido]

Bonjour,
Les colonnes tra_nego_honoraires sont sensées servir à identifier le nego bénificaire de l'honoraire :tra_nego_honoraires1 bénéficie de tra_honoraires1,
tra_nego_honoraires2 bénéficie de tra_honoraires2 ...

Merci pour tes solutions je vais tester.

-----------------------------------------

Néanmoins, j'ai une autre problématique :

J'ai une autre table Solde qui représente comme son nom l'indique le solde des honoraires de chaque négo.

Elle se présente comme suit :

nego_id | solde
1       56000
2       70000

Il s'agirait d'ajouté ce solde à la somme des honoraires de chaque nego dans la même requête que précédemment.
Jusqu'à maintenant je faisait le calcul en php dans la boucle de renvoi de la requête, en ayant mis préalablement tous les soldes dans un array (par une requête à part), mais ça n'est pas pratique si je veux trier les résultats par la suite.

Je pense qu'une simple jointure suffis mais je n'ai pas eu le temps de tester donc ne m'en voulais pas si la solution est évidente.

[punkoff]

oui une simple jointure suffira (avec un max sur le solde)

[philodido]

Pourquoi un max sur le solde ? il n'y a qu'un solde par négo.