Somme de plusieurs Select

Version imprimable

06/11/2009, 17h31
Morel26

Somme de plusieurs Select

Code:

Somme de plusieurs select
06/11/2009, 18h05
Waldar

Si on pouvait avoir la description des champs utiles de vos deux tables, ainsi qu'un petit jeu d'essai ce serait parfait !
06/11/2009, 18h30
pacmann

Salut !

Au passage, tu pourras également formater tes requêtes et mettre des balises code ! ;)

Une solution pourrais ressembler a cela, faisant le groupement dans chaque sous select. C est sans doute a adapter un peu...

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
 
SELECT t.iva, (SUM(POT_IVA)+SUM(POT_NVA)+SUM(POT_LG)+SUM(POT_LA)) AS POTENTIAL 
 
FROM(
 
 -- IVA --
 
    SELECT cus_id_pk iva, sum (nvl (CUS_CUSTOMERPOTENTIAL, 0)) AS pot_iva, 0 AS pot_nva, 0 AS pot_lg, 0 AS pot_la
    FROM customer_t 
    Group by cus_id_pk
 
    Union ALL
 
    -- NVA --
 
    SELECT h.iva, 0 AS pot_iva, sum (nvl (CUS_CUSTOMERPOTENTIAL, 0)) AS pot_nva, 0 AS pot_lg, 0 AS pot_la 
    FROM customer_t c, hierarchy_t h
    WHERE cus_id_pk = h.nva 
Group by h.iva
 
    Union ALL
 
    -- LG --
 
    SELECT h.iva, 0 AS pot_iva, 0 AS pot_nva, sum (nvl (CUS_CUSTOMERPOTENTIAL,0)) AS pot_lg, 0 AS pot_la 
    FROM customer_t c, hierarchy_t h
    WHERE cus_id_pk =h.lg
 Group by h.iva
    Union ALL
 
    -- LA --
 
    SELECT h.iva, 0 AS pot_iva, 0 AS pot_nva, 0 AS pot_lg, sum (nvl (CUS_CUSTOMERPOTENTIAL,0)) AS pot_la 
 
    FROM customer_t c, hierarchy_t h
 
    WHERE cus_id_pk =h.la 
group by h.iva
) t
GROUP BY t.IVA

09/11/2009, 14h29
Waldar

Est-ce que pour une même IVA un id_client peut se retrouver à plusieurs endroit ?
Dans votre exemple ce n'est pas le cas, mais est-ce une règle absolue ?

Dans ce cas c'est plus simple :

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
WITH Hierarchie AS
(
select  52 as iva, 664 as nva, 66 as lg, 847 as la from dual union all 
select 932       ,  74       , 95      ,  44       from dual union all
select  84       , 464       , null    , null      from dual union all
select 987       , null      , null    , null      from dual union all
select  46       ,  64       , 48      , null      from dual
)
  ,  Client AS
(
select  52 as clid, 'titi' as nom, 100 as pot from dual union all
select 932        , 'toto'       , 645        from dual union all
select 664        , 'lili'       , 847        from dual union all
select  74        , 'laLA'       , 635        from dual union all
select  46        , 'tete'       , 635        from dual union all
select  64        , 'tete'       , 832        from dual union all
select  66        , 'tutu'       , 344        from dual union all
select 847        , 'dalid'      , 772        from dual union all
select  95        , 'dad'        , 625        from dual union all
select  44        , 'cici'       ,  97        from dual union all
select  84        , 'nico'       , 827        from dual union all
select 464        , 'nene'       , 938        from dual union all
select  48        , 'jojo'       , 763        from dual union all
select 987        , 'nana'       , 837        from dual union all
select   1        , 'test'       , 150        from dual
)
  select c.clid, c.nom,
         case c.clid when h.iva then c.pot else 0 end as pot_iva,
         case c.clid when h.nva then c.pot else 0 end as pot_nva,
         case c.clid when h.lg  then c.pot else 0 end as pot_lg ,
         case c.clid when h.la  then c.pot else 0 end as pot_la
    from Client c  
         left outer join Hierarchie h
           on c.clid in (h.iva, h.nva, h.lg, h.la)
order by c.clid asc;
 
CLID	NOM	POT_IVA	POT_NVA	POT_LG	POT_LA
1	test	0	0	0	0
44	cici	0	0	0	97
46	tete	635	0	0	0
48	jojo	0	0	763	0
52	titi	100	0	0	0
64	tete	0	832	0	0
66	tutu	0	0	344	0
74	laLA	0	635	0	0
84	nico	827	0	0	0
95	dad	0	0	625	0
464	nene	0	938	0	0
664	lili	0	847	0	0
847	dalid	0	0	0	772
932	toto	645	0	0	0
987	nana	837	0	0	0

J'ai rajouté un client sans hierarchie, si vous n'en avez pas besoin remplacez la jointure externe par une jointure forte.

Comme ça peut-être alors ?

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
WITH Hierarchie AS
(
select  52 as iva, 664 as nva, 66 as lg, 847 as la from dual union all 
select 932       ,  74       , 95      ,  44       from dual union all
select  84       , 464       , null    , null      from dual union all
select 987       , null      , null    , null      from dual union all
select  46       ,  64       , 48      , null      from dual
)
  ,  Client AS
(
select  52 as clid, 'titi' as nom, 100 as pot from dual union all
select 932        , 'toto'       , 645        from dual union all
select 664        , 'lili'       , 847        from dual union all
select  74        , 'laLA'       , 635        from dual union all
select  46        , 'tete'       , 635        from dual union all
select  64        , 'tete'       , 832        from dual union all
select  66        , 'tutu'       , 344        from dual union all
select 847        , 'dalid'      , 772        from dual union all
select  95        , 'dad'        , 625        from dual union all
select  44        , 'cici'       ,  97        from dual union all
select  84        , 'nico'       , 827        from dual union all
select 464        , 'nene'       , 938        from dual union all
select  48        , 'jojo'       , 763        from dual union all
select 987        , 'nana'       , 837        from dual union all
select   1        , 'test'       , 150        from dual
)
  select h.iva,
         sum(c.pot) as pot_tot
    from Hierarchie h  
         inner join Client c
           on c.clid in (h.iva, h.nva, h.lg, h.la)
group by h.iva
order by h.iva asc;
 
IVA	POT_TOT
46	2230
52	2063
84	1765
932	2002
987	837

09/11/2009, 17h20
Waldar

Le prédicat de jointure !
09/11/2009, 17h40
Morel26

Merci beaucoup pour ta reponse, je comprend mieux maintenant
10/11/2009, 15h46
Morel26

Bounjour Waldar

Je me suis rendu compte que pour un IVA donné un ID_client peut se retrouver a plusieurs endroits, alors lorsque je fais des regroupement avec le clause GROUP BY il y a les potentiels qui se repetent

Merci pour la reponse
10/11/2009, 15h57
Waldar

Un petit exemple pour illustrer ?

Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT iva,nva,SUM(POT_IVA_LG) AS POT_IVA_NVA FROM ( SELECT h.iva,h.nva, lg, sum (c.pot) AS pot_IVA_LG FROM hierarchie h INNER JOIN client c ON c.id_client IN (h.iva, h.nva, h.lg, h.la) GROUP BY h.iva,h.nva, lg ORDER BY h.iva ASC )
Je somme tous les potentiels en les regroupant par nva, j’ai des valeurs trop grandes car il y a des potentiels qui se repetent

Exemple

Iva; nva ;lg ; pot
25; 12 ; 5 ; 100
25; 12 ; 7 ; 102
25 ; 12 ; 6 ; 106

Je veux le potentiel de 25 sachant que le potentiel de nva=12 est compte ici 3 fois (dans 100,102 et 106)et que le potentiel de lg peut egalement être compte plusieurs fois mais ce n’est pas le cas dans cet exemple

Merci

10/11/2009, 16h55
Waldar

J'ai quand même du mal avec votre besoin, ça change tout le temps !
Un coup les ID sont uniques, celui d'après ils ne le sont plus, vous avez besoin de regrouper par IVA, puis par IVA / NVA...

Honnêtement commencer par débrouissailler le problème fonctionnel, quand vous saurez ce que vous voudrez revenez ici, car là j'ai l'impression de perdre mon temps à pondre des requêtes qui ne sont de toutes façons pas adaptées.
10/11/2009, 17h24
Morel26

Excusez moi Waldar, c’est vrai que j’ai mal pose le problème

En fait j’ai besoin de regrouper par IVA mais le problème c’est que pour un IVA on a le meme nva qui se repete et donc son potentiel ce qui gonfle le résultat.

Exemple

Iva; nva ; lg ; pot
25; 12 ; 5 ; 100 = (40+60)
25; 12 ; 7 ; 102 = (40+62)
25 ; 12 ; 6 ; 106= (40+66)

40 étant la valeur de nva=12

Avec ma requête, le resultat me donne 308 (100+102+106) le potentiel de nva etant pris en compte ici 3 fois

Or le bon resultat est 228 (40+60+62+66)

Vous pouvez créer un exemple en modifiant cette table hierarchie qu'on a déjà utilisé ?

Code:

1
2
3
4
5
6
7
8
Table HIERARCHIE (du client)
 
IVA;  NVA;   LG;   LA
 52;  664;   66;  847
932;   74;   95;   44
 84;  464; null; null
987; null; null; null
 46;   64;   48; 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
25
26
27
 IVA;  NVA;   LG;   LA
 52;  664;   NULL;  841
 52;  664;   NULL;  842
 52;  664;   NULL;  843
52;  664;   NULL;  844
52;  664;   NULL;  845
52;  669;   92;  846
52;  669;   92;  847
52;  669;   92;  848
52;  669;   92;  849
52;  674;   10;  850
52;  674;   10;  851
52;  699;   10;  852
52;  656;   10;  853
52;  656;   10;  NULL
52;  656;   NULL;  NULL
NUll;  656;   66;  NULL
52;  656;   66;  854
52;  686;   66;  878
null;  NULL;   66;  857
52;  686;   66;  858
52;  686;   66;  895
52;  686;   66;  860
932;   74;   95; 861
 84;  464; NULL; NULL
987; NULL; NULL; NULL
 46;   64;   48; NULL

les valeurs de LA sont toujours uniques contrairement a IVA,NVA,et LG
car dernieres feuille de l'arbre
merci

Bon, la modélisation étant vraiment mauvaise, la requête n'est pas beaucoup meilleure...

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
with hierarchie as
(
select  52 as iva, 664 as nva, null as lg, 841 as la from dual union all 
select  52       , 664       , null      , 842       from dual union all
select  52       , 664       , null      , 843       from dual union all
select  52       , 664       , null      , 844       from dual union all
select  52       , 664       , null      , 845       from dual union all
select  52       , 669       , 92        , 846       from dual union all
select  52       , 669       , 92        , 847       from dual union all
select  52       , 669       , 92        , 848       from dual union all
select  52       , 669       , 92        , 849       from dual union all
select  52       , 674       , 10        , 850       from dual union all
select  52       , 674       , 10        , 851       from dual union all
select  52       , 699       , 10        , 852       from dual union all
select  52       , 656       , 10        , 853       from dual union all
select  52       , 656       , 10        , null      from dual union all
select  52       , 656       , null      , null      from dual union all
select null      , 656       , 66        , null      from dual union all
select  52       , 656       , 66        , 854       from dual union all
select  52       , 686       , 66        , 878       from dual union all
select null      , null      , 66        , 857       from dual union all
select  52       , 686       , 66        , 858       from dual union all
select  52       , 686       , 66        , 895       from dual union all
select  52       , 686       , 66        , 860       from dual union all
select 932       ,  74       , 95        , 861       from dual union all
select  84       , 464       , null      , null      from dual union all
select 987       , null      , null      , null      from dual union all
select  46       ,  64       , 48        , null      from dual
)
  ,  client as
(
select  52 as clid, 'titi' as nom, 100 as pot from dual union all
select 932        , 'toto'       , 645        from dual union all
select 841        , 'lili'       , 847        from dual union all
select 842        , 'laLA'       , 635        from dual union all
select 848        , 'tete'       , 635        from dual union all
select  64        , 'tete'       , 832        from dual union all
select  66        , 'tutu'       , 344        from dual union all
select 847        , 'dalid'      , 772        from dual union all
select  95        , 'dad'        , 625        from dual union all
select  44        , 'cici'       ,  97        from dual union all
select  84        , 'nico'       , 827        from dual union all
select 464        , 'nene'       , 938        from dual union all
select  48        , 'jojo'       , 763        from dual union all
select 850        , 'nana'       , 837        from dual union all
select 851        , 'test'       , 150        from dual
)
  ,  iva_d as
(
select distinct iva from hierarchie
)
  ,  nva_d as
(
select distinct iva, nva from hierarchie
)
  ,  nva_p as
(
  select h.iva, coalesce(sum(c.pot), 0) as pot_nva
    from nva_d h
         left outer join client c
           on c.clid = h.nva
group by h.iva
)
  ,  lg_d as
(
select distinct iva, lg from hierarchie
)
  ,  lg_p as
(
  select h.iva, coalesce(sum(c.pot), 0) as pot_lg
    from lg_d h
         left outer join client c
           on c.clid = h.lg
group by h.iva
)
  ,  la_p as
(
  select h.iva, coalesce(sum(c.pot), 0) as pot_la
    from hierarchie h  
         left outer join client c
           on c.clid = h.la
group by h.iva
)
  select i.iva,
         sum(n.pot_nva + g.pot_lg + a.pot_la + coalesce(c.pot, 0)) as pot_tot
    from iva_d i
         inner join nva_p n
           on coalesce(n.iva, -1) = coalesce(i.iva, -1)
         inner join lg_p g
           on coalesce(g.iva, -1) = coalesce(i.iva, -1)
         inner join la_p a
           on coalesce(a.iva, -1) = coalesce(i.iva, -1)
         left outer join client c
           on c.clid = i.iva
group by i.iva
order by 1 asc nulls first;
 
IVA	POT_TOT
	344
46	1595
52	4320
84	1765
932	1270
987	0

J'ai trouvé une requête beaucoup plus simple :

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
with hierarchie as
(
select  52 as iva, 664 as nva, null as lg, 841 as la from dual union all 
select  52       , 664       , null      , 842       from dual union all
select  52       , 664       , null      , 843       from dual union all
select  52       , 664       , null      , 844       from dual union all
select  52       , 664       , null      , 845       from dual union all
select  52       , 669       , 92        , 846       from dual union all
select  52       , 669       , 92        , 847       from dual union all
select  52       , 669       , 92        , 848       from dual union all
select  52       , 669       , 92        , 849       from dual union all
select  52       , 674       , 10        , 850       from dual union all
select  52       , 674       , 10        , 851       from dual union all
select  52       , 699       , 10        , 852       from dual union all
select  52       , 656       , 10        , 853       from dual union all
select  52       , 656       , 10        , null      from dual union all
select  52       , 656       , null      , null      from dual union all
select null      , 656       , 66        , null      from dual union all
select  52       , 656       , 66        , 854       from dual union all
select  52       , 686       , 66        , 878       from dual union all
select null      , null      , 66        , 857       from dual union all
select  52       , 686       , 66        , 858       from dual union all
select  52       , 686       , 66        , 895       from dual union all
select  52       , 686       , 66        , 860       from dual union all
select 932       ,  74       , 95        , 861       from dual union all
select  84       , 464       , null      , null      from dual union all
select 987       , null      , null      , null      from dual union all
select  46       ,  64       , 48        , null      from dual
)
  ,  client as
(
select  52 as clid, 'titi' as nom, 100 as pot from dual union all
select 932        , 'toto'       , 645        from dual union all
select 841        , 'lili'       , 847        from dual union all
select 842        , 'laLA'       , 635        from dual union all
select 848        , 'tete'       , 635        from dual union all
select  64        , 'tete'       , 832        from dual union all
select  66        , 'tutu'       , 344        from dual union all
select 847        , 'dalid'      , 772        from dual union all
select  95        , 'dad'        , 625        from dual union all
select  44        , 'cici'       ,  97        from dual union all
select  84        , 'nico'       , 827        from dual union all
select 464        , 'nene'       , 938        from dual union all
select  48        , 'jojo'       , 763        from dual union all
select 850        , 'nana'       , 837        from dual union all
select 851        , 'test'       , 150        from dual
)
  ,  all_cli as
(
  select distinct h.iva, c.clid
    from hierarchie h  
         left outer join client c
           on c.clid in (h.iva, h.nva, h.lg, h.la)
)
select ac.iva, sum(coalesce(cl.pot,0)) as pot_tot
  from all_cli ac
       left outer join client cl
         on cl.clid = ac.clid
group by ac.iva
order by ac.iva asc nulls first;
 
IVA	POT_TOT
	344
46	1595
52	4320
84	1765
932	1270
987	0

15/11/2009, 18h18
Morel26

Merci Waldar,

Votre réponse a bien résolu mon problème