Afficher la somme globale d'une colonne via SQL

Version imprimable

19/12/2012, 16h28
mohe27

Afficher la somme globale d'une colonne via SQL

Bonjour les amis,

Voilà je voudrai savoir s'il est possible d'afficher le global (somme) d'une colonne en fin du resultat d'une requête comme le montre l'exemple ci-dessous:

Produit | Qté
-------------
article 1| 10
article 2| 35
article 3| 20
-------------
Global | 65

merci

Hello,

Je le ferai comme ca.

Code:

1
2
3
4
5
6
7
8
9
10
11
 
 
WITH C AS
  (SELECT 'Article 1' Lib, 1 Qte FROM DUAL UNION ALL
   SELECT 'Article 2' , 2 FROM DUAL UNION ALL
   SELECT 'Article 3' , 5 FROM DUAL UNION ALL
   SELECT 'Article 4' , 20 FROM DUAL UNION ALL
   SELECT 'Article 5' , 29 FROM DUAL UNION ALL
   SELECT 'Article 6' , 40 FROM DUAL)
   SELECT nvl (lib,'Global'), sum (Qte) Qte FROM C
   GROUP BY   ROLLUP (Lib)

jko

Le nvl(lib,'Global') fonctionnera sur cet exemple mais c'est une mauvaise pratique.
Il vaut mieux tester la valeur du grouping :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH MaTable AS
(
SELECT 'Article 1' as Lib,  1 as Qte FROM DUAL UNION ALL
SELECT 'Article 2'       ,  2        FROM DUAL UNION ALL
SELECT 'Article 3'       ,  5        FROM DUAL UNION ALL
SELECT 'Article 4'       , 20        FROM DUAL UNION ALL
SELECT 'Article 5'       , 29        FROM DUAL UNION ALL
SELECT 'Article 6'       , 40        FROM DUAL
)
  SELECT case grouping(lib) when 1 then 'Global' else lib end as Lib
       , sum (Qte)                                            as Qte
    FROM MaTable
GROUP BY ROLLUP(Lib);

Citation:

Envoyé par Waldar

Le nvl(lib,'Global') fonctionnera sur cet exemple mais c'est une mauvaise pratique.
Il vaut mieux tester la valeur du grouping :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH MaTable AS
(
SELECT 'Article 1' as Lib,  1 as Qte FROM DUAL UNION ALL
SELECT 'Article 2'       ,  2        FROM DUAL UNION ALL
SELECT 'Article 3'       ,  5        FROM DUAL UNION ALL
SELECT 'Article 4'       , 20        FROM DUAL UNION ALL
SELECT 'Article 5'       , 29        FROM DUAL UNION ALL
SELECT 'Article 6'       , 40        FROM DUAL
)
  SELECT case grouping(lib) when 1 then 'Global' else lib end as Lib
       , sum (Qte)                                            as Qte
    FROM MaTable
GROUP BY ROLLUP(Lib);

Voui, j'avais oublié cette fonction grouping!

Merci
jko

Citation:

Envoyé par Waldar

Le nvl(lib,'Global') fonctionnera sur cet exemple mais c'est une mauvaise pratique.
Il vaut mieux tester la valeur du grouping :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH MaTable AS
(
SELECT 'Article 1' as Lib,  1 as Qte FROM DUAL UNION ALL
SELECT 'Article 2'       ,  2        FROM DUAL UNION ALL
SELECT 'Article 3'       ,  5        FROM DUAL UNION ALL
SELECT 'Article 4'       , 20        FROM DUAL UNION ALL
SELECT 'Article 5'       , 29        FROM DUAL UNION ALL
SELECT 'Article 6'       , 40        FROM DUAL
)
  SELECT case grouping(lib) when 1 then 'Global' else lib end as Lib
       , sum (Qte)                                            as Qte
    FROM MaTable
GROUP BY ROLLUP(Lib);

Excellent :ccool:
Je viens d'apprendre grouping, je vois qu'elle s'associe avec cube, peut tu me l'expliquer?

20/12/2012, 10h53
mohe27

merci les amis,

je vais tester ça sur un cas réel et je vous reviens :)

:ccool:

Citation:

Envoyé par islamov2000

Excellent :ccool:
Je viens d'apprendre grouping, je vois qu'elle s'associe avec cube, peut tu me l'expliquer?

Voici 2 exemples:

Avec rollup:

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
 
 
WITH MaTable AS
(
SELECT 'Categ 1' AS Categ,  'Article 1' as Lib,1 AS Qte FROM DUAL UNION ALL
SELECT 'Categ 1'       ,  'Article 2'       ,  2        FROM DUAL UNION ALL
SELECT 'Categ 2'       ,  'Article 3'       ,  5        FROM DUAL UNION ALL
SELECT 'Categ 3'       ,  'Article 4'       , 20        FROM DUAL UNION ALL
SELECT 'Categ 3'       ,  'Article 5'       , 29        FROM DUAL UNION ALL
SELECT 'Categ 2'       ,  'Article 6'       , 40        FROM DUAL
)
  SELECT 
  case grouping (Categ) 
     when 1 then 'Global Category' 
     else  Categ
    end AS Categ,
    case grouping (lib) 
     when 1 then 'Global Lib' 
     else  lib
    end AS Lib,
    sum (Qte)   AS Qte
    FROM MaTable
GROUP BY rollup(categ,Lib)
order by Categ,Lib
 
CATEG           LIB        QTE
--------------- ---------- ---
Categ 1         Article 1    1 
Categ 1         Article 2    2 
Categ 1         Global Lib   3 
Categ 2         Article 3    5 
Categ 2         Article 6   40 
Categ 2         Global Lib  45 
Categ 3         Article 4   20 
Categ 3         Article 5   29 
Categ 3         Global Lib  49 
Global Category Global Lib  97

Avec cube:

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
 
 
WITH MaTable AS
(
SELECT 'Categ 1' AS Categ,  'Article 1' as Lib,1 AS Qte FROM DUAL UNION ALL
SELECT 'Categ 1'       ,  'Article 2'       ,  2        FROM DUAL UNION ALL
SELECT 'Categ 2'       ,  'Article 3'       ,  5        FROM DUAL UNION ALL
SELECT 'Categ 3'       ,  'Article 4'       , 20        FROM DUAL UNION ALL
SELECT 'Categ 3'       ,  'Article 5'       , 29        FROM DUAL UNION ALL
SELECT 'Categ 2'       ,  'Article 6'       , 40        FROM DUAL
)
  SELECT 
  case grouping (Categ) 
     when 1 then 'Global Category' 
     else  Categ
    end AS Categ,
    case grouping (lib) 
     when 1 then 'Global Lib' 
     else  lib
    end AS Lib,
    sum (Qte)   AS Qte
    FROM MaTable
GROUP BY cube(categ,Lib)
order by Categ,Lib
 
CATEG           LIB        QTE
--------------- ---------- ---
Categ 1         Article 1    1 
Categ 1         Article 2    2 
Categ 1         Global Lib   3 
Categ 2         Article 3    5 
Categ 2         Article 6   40 
Categ 2         Global Lib  45 
Categ 3         Article 4   20 
Categ 3         Article 5   29 
Categ 3         Global Lib  49 
Global Category Article 1    1 
Global Category Article 2    2 
Global Category Article 3    5 
Global Category Article 4   20 
Global Category Article 5   29 
Global Category Article 6   40 
Global Category Global Lib  97

Et une variante avec grouping sets

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
 
WITH MaTable AS
(
SELECT 'Categ 1' AS Categ,  'Article 1' as Lib,1 AS Qte FROM DUAL UNION ALL
SELECT 'Categ 1'       ,  'Article 2'       ,  2        FROM DUAL UNION ALL
SELECT 'Categ 2'       ,  'Article 3'       ,  5        FROM DUAL UNION ALL
SELECT 'Categ 3'       ,  'Article 4'       , 20        FROM DUAL UNION ALL
SELECT 'Categ 3'       ,  'Article 5'       , 29        FROM DUAL UNION ALL
SELECT 'Categ 2'       ,  'Article 6'       , 40        FROM DUAL
)
  SELECT 
  Categ,lib,
    sum (Qte)   AS Qte
    FROM MaTable
GROUP BY grouping sets(categ,Lib,null)
order by Categ,Lib
 
CATEG   LIB       QTE
------- --------- ---
Categ 1             3 
Categ 2            45 
Categ 3            49 
        Article 1   1 
        Article 2   2 
        Article 3   5 
        Article 4  20 
        Article 5  29 
        Article 6  40 
                   97

jko

20/12/2012, 18h06
mohe27

Merci beaucoup les amis :)
j'ai pu ajuster tous ça sur un cas réel, ça marche à merveille.

Encore merci pour le coup de main, chapeau :ccool: