Discussion :

[ballantine's]

Bonjour,

je travaille sur une requête depuis un petit moment et je n'arrive pas à m'en sortir, je fais donc appel à vous pour m'éclairer.

Voici les tables dont j'ai besoin dans ma requête:

agents (num_agent,nni,nom,prenom,code_equipe)
offres(code_offre,libelle_offre,code_canal)
prod(num_prod,num_agent,date,prod)
objectifs(code_offre,date_deb,date_fin,annee,obj)
ventes(num_vente,code_offre,num_agent,code_equipe)

Je souhaite en faite obtenir une liste des agents, avec le libelle de l'offre (uniquement si ils en on vendu) le nombre vendu et le total de sa production.


j'en suis ici :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT nni,nom,prenom,v.libelle_offre,v.nbvente, p.prod FROM agents
LEFT OUTER JOIN (SELECT v.num_agent,of.libelle_offre,count(v.num_vente) AS nbvente 
FROM ventes v,offres of WHERE v.code_offre = of.code_offre 
AND v.code_equipe = 'EQ_ANGOULEME_01' 
AND v.date_vente between '2010-09-20' and '2010-09-24' 
GROUP BY v.code_offre)
 v ON v.num_agent = agents.num_agent
LEFT OUTER JOIN (select num_agent,sum(prod) as prod 
from prod
where num_agent in (SELECT distinct num_agent from ventes, offres where ventes.code_offre = offres.code_offre AND date_vente between '2010-09-20' and '2010-09-24' AND code_canal = 'crc_qe')
GROUP BY num_agent
) p on p.num_agent = agents.num_agent

cette requête n'est pas la bonne car je récupère pas mal de nul :

Voici une parie des résultats que j'obtiens.
nni nom prenom libelle_offre nbvente prod
I13456 MENTAL ELODIE NULL NULL NULL
I20153 MOT LAURENT NULL NULL NULL
G17056 TR FREDERIC suivi conso 27 588
H30557 ALI SEVERINE NULL NULL NULL
D58454 AUP EMILIE NULL NULL 431
D13456 BAR NICOLAS NULL NULL NULL
C75305 BOL PATRICK NULL NULL NULL
G31154 BOUR VANESSA NULL NULL 359
G58454 CHIB FIROUZE NULL NULL 617
I21253 GAUB STEPHANE NULL NULL NULL
J95957 LAUB TIFFANY NULL NULL NULL
E59448 LE MARIE LAURE NULL NULL 455

Je voudrais que le libelle de l'offre ne soit jamais null que le nombre de vente soit toujours >0. La prod quand à elle peut être null. Malheureusement je ne m'en sors pas, pourriez-vou m'aider.

Merci.

[punkoff]

Bonjour,

Vu que vous n'avez pas spécifié votre sgbd, ni un jeu de donnée, voici une piste qui peut ne pas marcher chez vous :

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
 
with tmp_offre as (
select a.num_agent, c.libelle_offre, count(*) as cntVente
from agents a
inner join ventes b on a.num_agent = b.num_agent
inner join offres c on b.code_offre = c.code_offre
group by a.num_agent, c.libelle_offre),
tmp_prod as (
select num_agent, count(*) as cntProd
from prod
group by num_agent)
 
 
select a.num_agent, a.libelle_offre, a.cntVente, b.cntProd
from tmp_offre a
left outer join tmp_prod b on a.num_agent = b.num_agent

[ballantine's]

Ah oui exact, je travaille sous MySQL et en effet cela ne fonctionne pas.

Voici un jeu de données

agents
1 g00000 bob val eq_ang_01
2 a11111 bac yves eq_ang_01
3 t88566 rouf bertrand eq_ang_01
4 t99531 jer marc eq_ang_02

offres
email_qe email crc_qe
sc_qe suivi conso crc_qe
afe_simm_part afe crc_simm_part

prod
1 1 2010-09-20 10
2 1 2010-09-22 20
3 1 2010-09-23 15
4 1 2010-09-24 15
5 3 2010-09-21 12
6 3 2010-09-22 40
7 2 2010-09-21 10

objectifs
email_qe 2010-07-05 2010-10-10 2010 0.25
sc_qe 2010-07-05 2010-10-10 2010 11.75
afe_simm_part 2010-07-05 2010-10-10 2010 6.25

ventes
1 email_qe 1 eq_ang_01
2 email_qe 1 eq_ang_01
3 sc_qe 1 eq_ang_01
4 email_qe 3 eq_ang_01
5 email_qe 4 eq_ang_01

[punkoff]

Donc oui, les structures en with ne sont pas supportées.

Le problème des libellés à null vient de votre left outer join.

Cette close ramène tous les éléments de gauches et, si il peut, les éléments de droites.

Utilisez à la place un inner join.

Ce cours l'expliquera mieux que moi :
http://sqlpro.developpez.com/cours/sqlaz/jointures/

Ensuite regardez comment on utilise les group by / count / sum car vous mixez un peu tout :
http://sqlpro.developpez.com/cours/sqlaz/ensembles/

A partir de là je penses que vous y arriverez car vous n'êtes pas très loin de la solution

[ballantine's]

oui je m'en suis rendu compte juste avant votre post...

erreur toute bête

Merci beaucoup en tout cas

[punkoff]

par contre ceci n'est pas bon je penses

Code :

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

1
2
3
4
5
LEFT OUTER JOIN (SELECT num_agent,sum(prod) AS prod 
FROM prod
WHERE num_agent IN (SELECT DISTINCT num_agent FROM ventes, offres WHERE ventes.code_offre = offres.code_offre AND date_vente BETWEEN '2010-09-20' AND '2010-09-24' AND code_canal = 'crc_qe')
GROUP BY num_agent
) p ON p.num_agent = agents.num_agent

Ici vous fait une addition de ?? (alors qu'il faudrait faire un count(*)) ou alors c'est un spécifique mysql que je ne connait pas

[ballantine's]

non non, c'est bien une somme que je veux faire .

cela étant j'ai de nouveau un problème sur le même type de requête, je ne récupère pas toutes mes ventes et ne comprend pas pourquoi :

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
 
SELECT nni, nom, prenom, v.libelle_offre, v.nbvente, b.bo
 FROM agents
 INNER JOIN (
 SELECT v.num_agent, v.code_offre, of.libelle_offre, count( v.num_vente ) AS nbvente
 FROM ventes v, offres of
 WHERE v.code_offre = of.code_offre
 AND v.code_equipe = 'EQ_ANGOULEME_02'
 AND v.date_vente = '2010-10-06'
 AND code_canal = 'bo_qe'
 GROUP BY v.num_agent, v.code_offre
 )v ON v.num_agent = agents.num_agent
 INNER JOIN (
 SELECT num_agent, code_offre, sum( bo ) AS bo
 FROM bo
 WHERE code_offre
 IN (
 SELECT code_offre
 FROM offres
 WHERE code_canal = 'bo_qe'
 )
 AND date_bo = '2010-10-06'
 GROUP BY num_agent, code_offre
 )b ON b.num_agent = agents.num_agent
 WHERE num_equipe = 'EQ_ANGOULEME_02'
 AND v.code_offre = b.code_offre

Voici un jeu de données

agents
1 g00000 bob val eq_ang_01
2 a11111 bac yves eq_ang_01
3 t88566 rouf bertrand eq_ang_01
4 t99531 jer marc eq_ang_02

offres
email_qe email crc_qe
sc_qe suivi conso crc_qe
afe_simm_part afe crc_simm_part

bo
635 2010-10-06 recla_e_bo_qe 1 150
636 2010-10-06 oxygene_bo_qe 2 60
637 2010-10-06 courrier_bo_qe 3 60
638 2010-10-06 oxygene_bo_qe 3 75
639 2010-10-06 bo_raf_a_faire_qe 4 90
640 2010-10-06 bo_raf_a_envoyer_qe 4 60

objectifs
email_qe 2010-07-05 2010-10-10 2010 0.25
sc_qe 2010-07-05 2010-10-10 2010 11.75
afe_simm_part 2010-07-05 2010-10-10 2010 6.25

ventes
1 courrier_bo_qe 1 eq_ang_01
2 oxygen_bo_qe 1 eq_ang_01
3 courrier_bo_qe 1 eq_ang_01
4 courrier_bo_qe 3 eq_ang_01
5 email_qe 4 eq_ang_01



Merci de m'aider une nouvelle fois.

[punkoff]

bonjour,

Dans les données que vous mettez, quelle vente en particulier ne remonte pas ?

Pour allez plus loin, dans la requête postée vous stipulez que pour votre table "offre", le code canal doit être égal à 'bo_qe' mais celui-ci n'existe pas dans votre jeu de donnée.

Peut-être est-il partiel seulement ?

[ballantine's]

ma table offres n'est pas complète dans ce que j'ai affiché.

bm_bo_qe Boite mail bo_qe 13 1
bo_gamme_qe Gamme bo_qe 5 1
bo_mes_groupees_qe MENS Groupées bo_qe 4 1
bo_npai_qe NPAI bo_qe 6 1
bo_pgi_qe PGI bo_qe 14 1
bo_qe_akio akio bo_qe 1 1
bo_raf_a_envoyer_qe RAF à envoyer bo_qe 8 1
bo_raf_a_faire_qe RAF à faire bo_qe 9 1
bo_raf_a_valider_qe RAF à valider bo_qe 7 1
courrier_bo_qe Courrier bo_qe 2 1
oxygene_bo_qe Oxygène bo_qe 12 1
recla_e_bo_qe Recla écrite bo_qe 10 1
recla_o_bo_qe Récla orale bo_qe 11 1


Ce ne sont pas toujours les mêmes qui ne remonte pas. ça donne l'impression d'être une remonté aléatoire

[punkoff]

Pour une même requête vous avez des résultats différents sans que les données ne changent ??

edit: ok oubliez ma phrase du dessous
si à la place de la clause select vous mettez un count(*) et que vous lancez plusieurs fois cette requête, le résultat est identique ?

[ballantine's]

Pas tout à fait selon l'équipe j'ai du changement, pourtant se sont les mêmes offres qui sont concernées

[punkoff]

ok, donc en regardant vos définitions de table + la requête on peut voir que (reprenez moi si je me trompe):

- un code équipe doit regrouper plusieurs agent différent.
- les ventes sont réalisée par 1 agent appartenant à une équipe.
- la prod est réalisée par un agent, indépendamment du reste.

Hors dans la requête posée tel quel, nous avons 2 jointures internes, une sur les ventes et une sur la prod.

De ce fait, on ramène seulement les ventes réalisées par des agents qui ont fait une prod.

Y a-t-il des agents qui ne font pas de production ?

[ballantine's]

c'est possible en effet

[ballantine's]

J'ai tenté avec right comme avec left outer join et j'ai toujours des remontées qui manquent. je ne vois pas où est l'erreur

Merci de votre aide

[soazig]

Bonjour,
J'ai regardé ta requete, et j'ai vu que l'alias v était utilisé pour la table vente et pour la sous requete, essaie de ne pas réutiliser d'alias.
De plus pour le deuxième inner join tu as mis la moitié du critère de jointure dans le on, et l'autre moitié dans le where, met tout dans le on .
Pour la sous requete v dans le premier inner join il manque le group by sur libelle_offre.
J'ai aussi remplacé l'alias of par of1 car il me semble que dans certains SGBD c'est un mot clé.
Voilà donc ce que je te propose.

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
 
SELECT nni, 
    nom,
    prenom, 
    v2.libelle_offre,
    v2.nbvente,
    b.bo
 FROM agents
 INNER JOIN (
         SELECT v.num_agent, v.code_offre, of1.libelle_offre, 
            count( v.num_vente ) AS nbvente
         FROM ventes v
            inner join offres of1
        on v.code_offre = of1.code_offre
         WHERE 
              v.code_equipe = 'EQ_ANGOULEME_02'
             AND v.date_vente = '2010-10-06'
             AND code_canal = 'bo_qe'
         GROUP BY v.num_agent, v.code_offre,of1.libelle_offre
         ) v2 ON v2.num_agent = agents.num_agent
 INNER JOIN (
     SELECT num_agent, code_offre, sum( bo ) AS bo
     FROM bo
     WHERE code_offre
         IN (
         SELECT code_offre
         FROM offres
         WHERE code_canal = 'bo_qe'
         )
     AND date_bo = '2010-10-06'
     GROUP BY num_agent, code_offre
     )b ON b.num_agent = agents.num_agent
     AND v2.code_offre = b.code_offre
 WHERE num_equipe = 'EQ_ANGOULEME_02'

a+
Soazig