Comportement étrange de la fonction SUM()

**feldi** · 17/01/2012, 15h58

Bonjour, (encore moi !

)

pour un programme, je dois récupérer des valeurs (champ price de dcf) par vendeur et par date.
La ou il y a une subtilité, c'est dans la formule pour récupérer les valeurs qui m'intéresse : à une date J, on récupère les infos de J-8 à J-1.
Par exemple au 18/01/2012, on récupère du 10/01/2012 au 17/01/2012.

Normalement, cette partie est censée fonctionner dans mon programme.

Le code :

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
 
INSERT INTO PORTEFEUILLEAFFAIRE2 (DATEAFFAIRE, INITIALEVENDEUR, NOUVEAUXDEVIS, NBNOUVEAUXDEVIS, NUMSEMAINE)
SELECT  DISTINCT to_date(substr(qvs.date_create,1,8),'YYYY/MM/DD'),
        qcl.short_text1,
        dcf.price,
        count(dcf.price),
        to_char( to_date(to_date(substr(qvs.date_create,1,8),'YYYY/MM/DD'),'DD/MM/YYYY'), 'WW')
FROM (SELECT linequote, price, dateoffer
     FROM ...     ) dcf,
    (SELECT z_id_cart, num_line
    FROM ...    WHERE flag2 =1 AND id_item <> 'multi-produits'
        AND id_item <> 'price List'
        AND id_item <> 'simu') cla,
    qvs,
    (SELECT id_entity, short_text1
    FROM ...    WHERE id_class = 'quoUps' AND num_array = 0 AND num_line = 0) qcl
WHERE dcf.linequote = cla.num_line
    AND cla.z_id_cart = qvs.id_quote
    AND qcl.id_entity = qvs.id_quote
    AND to_date(dcf.dateoffer, 'YYYY\MM\DD HH24.MI.SS') between to_date(qvs.date_create, 'YYYYMMDD HH24MISS') - 8 and to_date(qvs.date_create, 'YYYYMMDD HH24MISS') - 1
GROUP BY qvs.date_create,
         qcl.short_text1
         ,dcf.price;

La ou j'ai un résultat étrange :

avec ce programme, j'obtiens 2 lignes (avec un filtre sur la date et initialeVendeur):

date | initialeVendeur | nouveauxDevis | nbNouveauxDevis | numSemaine
22/03/2011 vpe 4774 1 12
22/03/2011 vpe 8180 1 12

Ensuite le même code mais avec

Code :

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

1
2
 
sum(dcf.price)

au lieu de

Code :

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

1
2
 
dcf.price

et j'obtiens :

date | initialeVendeur | nouveauxDevis | nbNouveauxDevis | numSemaine
22/03/2011 vpe 4774 1 12
22/03/2011 vpe 12952 2 12

Alors que normalement je devrais avoir uniquement la ligne du dessous non ?

J'ai vérifié pour les valeurs et dans ma table dcf, pour une date entre le 14/03/2011 et le 21/03/2011, j'ai bien uniquement 2 chiffres (4774 et 8180).

Est-ce que vous voyez d'ou cela peut venir ? De ma requete ? D'autre chose ?

Merci !

**Rams7s** · 17/01/2012, 16h21

C'est pas tellement un comportement etrange de SUM, mais d'avantage une erreur de GROUP BY.

Vous faites un group by ..., dcf.priceEt du coup, comme vous avez un seul dcf.price, il n'ajoute rien du tout.

Comme c'est pas clair:

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
TMP@MINILHC >with tmp as (select mod(level,4) as n from dual connect by level<6) select n,sum(n) from tmp group by n order by n;
 
         N     SUM(N)
---------- ----------
         0          0
         1          2
         2          2
         3          3
 
Elapsed: 00:00:00.00
TMP@MINILHC >with tmp as (select mod(level,4) as n from dual connect by level<6) select n from tmp order by n;
 
         N
----------
         0
         1
         1
         2
         3

Dans le premier cas, la somme des N=1 c'est deux parce que 1 apparait deux fois. Mais la somme pour N=2 (resp 3) c'est 2 (resp 3) parce ce que ce nombre n'apparait qu'une fois.

**feldi** · 17/01/2012, 16h28

Envoyé par Rams7s

C'est pas tellement un comportement etrange de SUM, mais d'avantage une erreur de GROUP BY.

Vous faites un group by ..., dcf.priceEt du coup, comme vous avez un seul dcf.price, il n'ajoute rien du tout.

Comme c'est pas clair:
Dans le premier cas, la somme des N=1 c'est deux parce que 1 apparait deux fois. Mais la somme pour N=2 (resp 3) c'est 2 (resp 3) parce ce que ce nombre n'apparait qu'une fois.

OK (faudra que j'analyse un peu ce que tu as écrit) mais concrètement je dois faire quoi alors pour n'avoir plus qu'une ligne avec le sum() ?

**Waldar** · 17/01/2012, 16h58

Je pense que l'erreur porte plutôt sur la date de création.
Votre GROUP BY ne reflète pas votre SELECT, et en plus vous le leurrez avec un DISTINCT.

Ce n'est pas forcément faux, mais il faut comprendre ce que vous avez écrit.

Imaginons ce jeu de données avec simplement des nombres :

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 SR as
(
select 1.0 as nb from dual union all
select 1.5       from dual union all
select 2.0       from dual union all
select 3.0       from dual
)
select nb
  from sr;
 
        NB
----------
         1
       1.5
         2
         3

Vous dites, je veux faire un comptage par nombre, jusque-là ça va :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
  select nb, count(*)
    from SR
group by nb
order by 1;
 
        NB   COUNT(*)
---------- ----------
         1          1
       1.5          1
         2          1
         3          1

Par contre ensuite vos modifier l'affichage de votre donnée, donc vous changez le SELECT mais sans toucher à l'aggrégat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
  select floor(nb) as nb, count(*)
    from SR
group by nb
order by 1;
 
        NB   COUNT(*)
---------- ----------
         1          1
         1          1
         2          1
         3          1

Là vous vous dites, tient j'ai un doublon, et si je faisais un distinct en plus :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
  select distinct
         floor(nb) as nb, count(*)
    from SR
group by nb
order by 1;
 
        NB   COUNT(*)
---------- ----------
         1          1
         2          1
         3          1

Plus de doublon ! Magnifique !
Mais ce résultat est très différent de :

Code :

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

1
2
3
4
5
6
7
8
9
10
  select floor(nb) as nb, count(*)
    from SR
group by floor(nb) -- Ici vous définissez bien votre palier de regroupement
order by 1;
 
        NB   COUNT(*)
---------- ----------
         1          2
         2          1
         3          1

Pour faire court, dans votre GROUP BY, remplacez qvs.date_create par to_date(substr(qvs.date_create,1,8),'YYYY/MM/DD'), et supprimez votre DISTINCT qui vous induit en erreur.
Dans quasiment toutes les requêtes, le SELECT et le GROUP BY sont identiques et le DISTINCT inutile.