Obtenir le total et la moyenne

**tunis71187** · 18/12/2013, 07h23

Bonjour,
J'ai deux tables "entrée" et "sortie".
J'ai rencontré une difficulté pour obtenir le total des entrées ainsi que la moyenne obtenu des sorties par les entrées dans un mois.

Table Entrée:
(
id number primary key,
numero varchar2(10),
date_ent date
);
Table sortie:
(
id number primary key,
id_ent number foreign key,
numero varchar2(10),
date_sor date
)

j'ai essayé comme suit :

Code :

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

1
2
3
4
5
 
select count(e.date_ent) as total
from entree e, sortie s
where e.id=s.id_ent
and to_char(e.date_ent,'MM/YYYY')='12/2013'

il me reste que de diviser le nombre des sorties en mois de Janvier par le nombre des entrées en ce mois.

Merci pour vos propositions.

**mnitu** · 18/12/2013, 10h34

Ajoutez un petit jeu d'essai et préciser le résultat attendu et éventuellement les règles à appliquer.

**tunis71187** · 18/12/2013, 19h00

Par exemple:
Entrée:

1     1/2013   01/01/2013 
2     2/2013   20/01/2013
3     3/2013   10/08/2013
4     4/2013   15/10/2013
5     5/2013   30/11/2013

Sortie :

1    2    1/2013  05/06/2013
2    4    2/2013  30/10/2013
3    5    3/2013  10/12/2013

------------------------------------
l'ensemble des entrées est : 5
l'ensemble des sorties est : 3

donc comme résultat pour le mois de Janvier:

Total     Sortie       Moyenne    Pourcentage
    2       1           0.5         50%

Merci pour vos réponses.

**mnitu** · 19/12/2013, 11h14

Faite une jointure externe entre les deux tables et compter les id de ces deux tables.

**tunis71187** · 19/12/2013, 19h33

J'ai essayé ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
 
SELECT count(e.id) as Total, T.Sortie/count(e.id) as Moyenne
FROM entree e
left outer join (select count (s.id) as Sortie, id_ent
                    from sortie s
                    group by numero_be
                   )T
on e.id=T.id_ent
where to_char(e.date_ent,'MM/YYYY')='11/2013'

Mais j'ai un message :

Code :

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

ORA-00937: la fonction de groupe ne porte pas sur une groupe simple.

**punkoff** · 20/12/2013, 09h12

bonjour,

http://sqlpro.developpez.com/cours/sqlaz/ensembles/#L1

**mnitu** · 20/12/2013, 09h43

A partir de votre jeu d'essai faite une jointure externe entre les deux tables. Examinez le résultat et écrivez la requête qui compte des id de vos deux tables sur cette jointure.

**tunis71187** · 20/12/2013, 13h26

j'ai déjà fait la jointure externe entre les deux tables et le count des id aussi mais je n'ai pas obtenu le bon résultat.

**skuatamad** · 20/12/2013, 13h35

Envoyé par tunis71187

j'ai déjà fait la jointure externe entre les deux tables et le count des id aussi mais je n'ai pas obtenu le bon résultat.

Postez ce que vous faîtes, vos messages d'erreur ou les résultats obtenus et non conformes...

Code :

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

1
2
3
4
SELECT count(e.id) AS total, count(s.id) as Sortie
  FROM entree e 
  left join sortie s on s.id_ent = e.id
 WHERE to_char(e.date_ent,'MM/YYYY')='12/2013'

**tunis71187** · 20/12/2013, 16h58

Merci skuatamad, ça marche: j'ai pu afficher le 'Total', le 'Sortie' et le pourcentage :

Code :

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

1
2
3
4
5
 
SELECT count(e.id) AS total, count(s.id) AS Sortie, trunc(count(s.id)/count(e.id)*100) AS Pourcentage
  FROM entree e 
  LEFT JOIN sortie s ON s.id_ent = e.id
 WHERE to_char(e.date_ent,'MM/YYYY')='12/2013'

Je veux juste ajouter une condition qui consiste à afficher le résultat de la requête ci dessus si la différence entre la date de sortie et la date d'entrée est supérieure à 5 jours.

le problème s'impose si on a des entrées sans sorties.

Merci pour les propositions.

**tunis71187** · 21/12/2013, 19h50

J'ai modifié la requête :

Code :

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

1
2
3
4
5
6
 
SELECT count(e.id) AS total, count(s.id) AS Sortie, trunc(count(s.id)/count(e.id)*100) AS Pourcentage
  FROM entree e 
  LEFT JOIN sortie s ON s.id_ent = e.id
 WHERE to_char(e.date_ent,'MM/YYYY')='12/2013'
 and (s.date_sor - e.date_ent) < 5

Considérant l'exemple d'avoir une entrée sans sortie en mois de décembre, donc la requête ci dessus déclenche l'erreur :

Code :

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

ORA-01476 : le diviseur est égal à zéro.

donc si on a cinq entrées en un mois quelconque. Parmi ces entrées on peut avoir des entrées ayants des sorties dans un délai de plus ou moins de 5 jours et d'autres n'ont pas de sorties.

Comment je peux résoudre ce problème ?

**tunis71187** · 23/12/2013, 09h00

Soit l'exemple suivant :
Table Entree:

1     1/2013   01/12/2013 
2     2/2013   10/12/2013
3     3/2013   15/12/2013
4     4/2013   20/12/2013
5     5/2013   23/12/2013

Table Sortie :

1    1    1/2013  16/12/2013
2    4    2/2013  23/12/2013

On a 5 entrées et deux sorties pour les entrées 1 et 4.
Parmi ces sorties la sortie 2 est réalisé moins de 5 jours.
la requête doit donner pour le mois de décembre et la différence de dates entre l'entrée et la sortie est inférieure à 5 :

Total   Sortie   Moyenne    Pourcentage
  5         1       0.2             20%

Je suppose que ça peut éclaircir un peu le problème.

**skuatamad** · 23/12/2013, 09h12

En plaçant s.date_sor dans le filtre (WHERE) vous transformez votre jointure externe en jointure interne :
Jointure externe et condition de restriction.

Vous pouvez faire :

Code :

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

1
2
3
4
5
6
SELECT count(e.id) AS total, count(s.id) AS Sortie, trunc(count(s.id)/count(e.id)*100) AS Pourcentage
  FROM entree e 
  LEFT JOIN sortie s 
    ON s.id_ent = e.id
   and (s.date_sor - e.date_ent) < 5
 WHERE to_char(e.date_ent,'MM/YYYY')='12/2013'

Ou vous pouvez conditionner le décompte des sorties :

Code :

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

1
2
3
4
5
6
SELECT count(e.id) AS total, 
       sum(case when s.date_sor - e.date_ent then 1 end) AS Sortie, 
       trunc(sum(case when s.date_sor - e.date_ent then 1 end) / count(e.id)*100) AS Pourcentage
  FROM entree e 
  LEFT JOIN sortie s ON s.id_ent = e.id
 WHERE to_char(e.date_ent,'MM/YYYY')='12/2013'

**tunis71187** · 23/12/2013, 13h44

Merci skuatamad pour ta réponse.

* Pour la première requête : il manque la prise en compte des entrées sans sorties.

* Pour la deuxième requête : il m'affiche un erreur :

Code :

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

ORA-00920 : opérateur relationnel non valide.

se pointe sur le 'then'.

**skuatamad** · 23/12/2013, 15h23

Envoyé par tunis71187

* Pour la première requête : il manque la prise en compte des entrées sans sorties.

Je ne comprends pas, voici le test :

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
35
36
37
38
39
40
41
42
43
44
45
SQL> with entree as (
  2  select 1 as id, to_date('01/12/2013','dd/mm/yyyy') as date_ent from dual union all
  3  select 2     , to_date('10/12/2013','dd/mm/yyyy') from dual union all
  4  select 3     , to_date('15/12/2013','dd/mm/yyyy') from dual union all
  5  select 4     , to_date('20/12/2013','dd/mm/yyyy') from dual union all
  6  select 5     , to_date('23/12/2013','dd/mm/yyyy') from dual
  7  ),
  8  sortie as (
  9  select 1 as id,   1 as id_ent  , to_date('16/12/2013','dd/mm/yyyy') as date_sor from dual union all
 10  select 2 ,   4   , to_date('23/12/2013','dd/mm/yyyy') from dual
 11  )
 12  SELECT count(e.id) AS total, count(s.id) AS Sortie, trunc(count(s.id)/count(e.id)*100) AS Pourcentage
 13    FROM entree e
 14    LEFT JOIN sortie s
 15      ON s.id_ent = e.id
 16     AND (s.date_sor - e.date_ent) < 5
 17   WHERE to_char(e.date_ent,'MM/YYYY')='12/2013';
 
     TOTAL     SORTIE POURCENTAGE
---------- ---------- -----------
         5          1          20
 
SQL> with entree as (
  2  select 1 as id, to_date('01/12/2013','dd/mm/yyyy') as date_ent from dual union all
  3  select 2     , to_date('10/12/2013','dd/mm/yyyy') from dual union all
  4  select 3     , to_date('15/12/2013','dd/mm/yyyy') from dual union all
  5  select 4     , to_date('20/12/2013','dd/mm/yyyy') from dual union all
  6  select 5     , to_date('23/12/2013','dd/mm/yyyy') from dual
  7  ),
  8  sortie as (
  9  select 1 as id,   1 as id_ent  , to_date('16/12/2013','dd/mm/yyyy') as date_sor from dual union all
 10  select 2 ,   4   , to_date('23/12/2013','dd/mm/yyyy') from dual
 11  )
 12   SELECT count(e.id) AS total,
 13         sum(case when s.date_sor - e.date_ent < 5 then 1 end) AS Sortie,
 14         trunc(sum(case when s.date_sor - e.date_ent < 5 then 1 end) / count(e.id)*100) AS Pourcentage
 15    FROM entree e
 16    LEFT JOIN sortie s ON s.id_ent = e.id
 17   WHERE to_char(e.date_ent,'MM/YYYY')='12/2013';
 
     TOTAL     SORTIE POURCENTAGE
---------- ---------- -----------
         5          1          20
 
SQL>

Pour la 2eme requête j'avais oublié le <5

**tunis71187** · 24/12/2013, 11h28

Bonjour,

Merci skuatamad pour ta réponse.

j'ai une remarque : j'ai trouvé une différence sur quelques données de ma base réelle, la deuxième requête donne toujours des résultats exactes alors que la première je trouve parfois une différence je ne sais pas pourquoi mais j'ai utilisé la deuxième requête.

Il me reste que le traitement des résultats où il y a des entrées sans sorties pour un mois quelconque c'est à dire par exemple j'ai comme résultat :

Total Sortie Pourcentage
4

je veux juste s'il n'y a pas des sorties afficher un zéro au lieu de colonne vide.

**punkoff** · 24/12/2013, 11h32

utilisez un COALESCE

**tunis71187** · 24/12/2013, 11h41

pouvez vous me donner un exemple.

**al1_24** · 24/12/2013, 11h56

COALESCE est la version ANSI de la fonction NVL spécifique à Oracle.

**tunis71187** · 24/12/2013, 12h43

Comment je peux l'utiliser dans la requête suivante :

Code :

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

1
2
3
4
5
6
7
 
SELECT count(e.id) AS total,
        sum(case when s.date_sor - e.date_ent < 5 then 1 end) AS Sortie,
         trunc(sum(case when s.date_sor - e.date_ent < 5 then 1 end) / count(e.id)*100) AS Pourcentage
     FROM entree e
     LEFT JOIN sortie s ON s.id_ent = e.id
    WHERE to_char(e.date_ent,'MM/YYYY') = '12/2013';

Obtenir le total et la moyenne

SQL Oracle

Discussions similaires

Partager

Partager