Somme champs dans CTE avec lignes dupliquées

**freud** · 12/10/2018, 15h00

Bonjour à tous,

Comme indiqué dans l'intitulé, j'ai un problème dans une CTE en utilisant la fonction SUM dans plusieurs champs je m'attend qu'elle me renvoi qu'un seule ligne sommée par employé tandis qu'elle me renvoi un employé dupliqué.

Voici le code de la CTE:

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
46
47
48
49
50
 
CREATE OR ALTER PROCEDURE COTISATION_MOIS (
    ANNEE INTEGER,    
    MOIS INTEGER)
RETURNS (
    EMPLOYE_ID INTEGER,
    EMPLOYE_NOM VARCHAR(30),
    EMPLOYE_PRENOMS VARCHAR(30),
    ASSIETTE NUMERIC(15,2),
    COTISATION NUMERIC(15,2),
    JOUR_TRAVAILLER FLOAT,
    HEURE_TRAVAILLER FLOAT
    )
AS
BEGIN
FOR
WITH
COTISATION (EMPLOYE_ID,ASSIETTE,COTISATION,NB_JOUR,NB_HEURE)
AS
(
  SELECT  EMPLOYE_ID,SUM(SALAIRE_COTISABLE) AS ASSIETTE,SUM(RSS) AS COTISATION,
  SUM(JOUR_TRAVAILLER) AS JOUR_TRAVAILLER,SUM(HEURE_TRAVAILLER) AS HEURE_TRAVAILLER
  FROM SALAIRES
  WHERE MOIS=:MOIS AND ANNEE=:ANNEE
  GROUP BY 1
),
 
SALAIRE
AS
(
SELECT EMPLOYE_ID,EMPLOYE_NOM,EMPLOYE_PRENOMS
FROM SALAIRES
WHERE MOIS=:MOIS AND ANNEE=:ANNEE
),
 
TOTAL
AS
(
SELECT C.EMPLOYE_ID,S.EMPLOYE_NOM,S.EMPLOYE_PRENOMS,C.ASSIETTE,C.COTISATION,C.NB_JOUR,C.NB_HEURE
FROM COTISATION C
INNER JOIN SALAIRE S ON C.EMPLOYE_ID=S.EMPLOYE_ID
)
 
SELECT  EMPLOYE_ID,EMPLOYE_NOM,EMPLOYE_PRENOMS,ASSIETTE,COTISATION,NB_JOUR,NB_HEURE FROM TOTAL
 
INTO
 
:EMPLOYE_ID,:EMPLOYE_NOM,:EMPLOYE_PRENOMS,:ASSIETTE,:COTISATION,:JOUR_TRAVAILLER,:HEURE_TRAVAILLER
 DO SUSPEND;
END

Voici un petit jeu de données

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
 
CREATE TABLE SAL
(
EMPLOYE_ID INTEGER,
EMPLOYE_NOM VARCHAR(30),
EMPLOYE_PRENOMS VARCHAR(30),
SALAIRE_COTISABLE NUMERIC(15,2),
RSS NUMERIC(15,2),
JOUR_TRAVAILLER FLOAT,
HEURE_TRAVAILLER FLOAT,
MOIS INTEGER,
TRIMESTRE INTEGER,
ANNEE INTEGER)
 
 
 
INSERT INTO SAL (EMPLOYE_ID, EMPLOYE_NOM, EMPLOYE_PRENOMS, SALAIRE_COTISABLE, RSS, JOUR_TRAVAILLER, HEURE_TRAVAILLER, MOIS, TRIMESTRE, ANNEE)
VALUES (3, 'AAAA', 'BBBB', 25000, 2250, 13, 170, 12, 4, 2017);
 
 
INSERT INTO SAL (EMPLOYE_ID, EMPLOYE_NOM, EMPLOYE_PRENOMS, SALAIRE_COTISABLE, RSS, JOUR_TRAVAILLER, HEURE_TRAVAILLER, MOIS, TRIMESTRE, ANNEE)
VALUES (2, 'CCCC', 'DDDDD', 19000, 1710, 21, 165, 12, 4, 2017);
 
 
INSERT INTO SAL (EMPLOYE_ID, EMPLOYE_NOM, EMPLOYE_PRENOMS, SALAIRE_COTISABLE, RSS, JOUR_TRAVAILLER, HEURE_TRAVAILLER, MOIS, TRIMESTRE, ANNEE)
VALUES (1, 'EEEE', 'FFFF', 23500, 2115, 22, 170, 12, 4, 2017);
 
 
INSERT INTO SAL (EMPLOYE_ID, EMPLOYE_NOM, EMPLOYE_PRENOMS, SALAIRE_COTISABLE, RSS, JOUR_TRAVAILLER, HEURE_TRAVAILLER, MOIS, TRIMESTRE, ANNEE)
VALUES (1, 'EEEE', 'FFFF', 20000, 1800, 20, 140, 12, 4, 2017);

J'obtiens quatre lignes avec ce jeu mais si j’exécute à part le code ci-dessous en dehors de la CTE (celui de la 1ere requete du CTE: COTISATION) j'obtiens correctement les trois lignes au lieu des quatres.

Code :

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

1
2
3
4
5
6
 
SELECT  EMPLOYE_ID,SUM(SALAIRE_COTISABLE) AS ASSIETTE,SUM(RSS) AS COTISATION,
  SUM(JOUR_TRAVAILLER) AS JOUR_TRAVAILLER,SUM(HEURE_TRAVAILLER) AS HEURE_TRAVAILLER
  FROM SALAIRES
  WHERE MOIS=:MOIS AND ANNEE=:ANNEE
  GROUP BY 1

C'est à dire que EMPLOYE_ID n° 1 est affiché deux fois sommé

En vous remerciant de bien vouloir m'aider.

**SergioMaster** · 12/10/2018, 15h38

Bonjour,

A mon avis et à première vue le problème vient surtout du jeu de données et du manque de contraintes (index primaire etc...)
que la somme et le nombre de jour soit doublé pour le 0001 pour la CTE COTISATION est tout à fait logique
ce qui l'est moins c'est la CTE SALAIRE qui elle n'est pas groupée (si le doublon est accepté bien entendu)

enfin en dernière remarque, je ne vois pas trop l'intérêt de ces deux CTE, à mon avis le tout peut se faire en un simple SQL
Mais ce n'est qu'un premier coup d’œil rapide

**freud** · 12/10/2018, 18h31

En fait la table réelle (SALAIRES) contient une soixantaine de champs et je fait le SELECT sur 17 champs j'ai dû dupliquer cette table en prenant quelques champs et la poster pour simplifier (table SAL) mais c'est la même anomalie qui se produit que ce soit dans la table d'origine que sur celle que j'ai poster. Les tables ont un index primaire SALAIRE_ID. Le problème c'est que j'ai essayer de faire un simple SQL dans un 1er temps avec un SELECT SUM avec GROUP BY sur 13 champs les quatres autres concernes les champs à sommée. J'ai trouvé que ce n'etait pas normale de groupé sur 13 champs et ce n'est peut-être pas correct de plus j'obtenais le même résultat qu'avec la CTE sauf si j’élimine un champ le résultat sera correct.

Envoyé par SergioMaster

je ne vois pas trop l'intérêt de ces deux CTE, à mon avis le tout peut se faire en un simple SQL

Tout à fait d'accord avec vous mais je pensais pouvoir régler le problème en me rabattant sur une CTE.
Je simplifie le code de la CTE:

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
 
BEGIN
FOR
WITH
COTISATION (EMPLOYE_ID,ASSIETTE,COTISATION,NB_JOUR,NB_HEURE)
AS
(
  SELECT  EMPLOYE_ID,SUM(SALAIRE_COTISABLE) AS ASSIETTE,SUM(RSS) AS COTISATION,
  SUM(JOUR_TRAVAILLER) AS JOUR_TRAVAILLER,SUM(HEURE_TRAVAILLER) AS HEURE_TRAVAILLER
  FROM SAL
  WHERE MOIS=:MOIS AND ANNEE=:ANNEE
  GROUP BY 1
)
 
SELECT C.EMPLOYE_ID,S.EMPLOYE_NOM,S.EMPLOYE_PRENOMS,C.ASSIETTE,C.COTISATION,C.NB_JOUR,C.NB_HEURE
FROM COTISATION C INNER JOIN SALAIRES S ON C.EMPLOYE_ID=S.EMPLOYE_ID
 
INTO
 
:EMPLOYE_ID,:EMPLOYE_NOM,:EMPLOYE_PRENOMS,:ASSIETTE,:COTISATION,:JOUR_TRAVAILLER,:HEURE_TRAVAILLER
 DO SUSPEND;
END

Peut-que j'ai mal compris les CTE mais il semble que le résultat de la 1ere CTE (COTISATION) doit-être join avec la table SALAIRES par les champs EMPLOYE_ID pour rapatrier d'autres infos concernant la ligne. Ou, autrement dit, comment pourrais-je joindre le résultat de COTISATION qui est correct avec SALAIRES ?

Merci.

**freud** · 12/10/2018, 19h20

Envoyé par SergioMaster

A mon avis et à première vue le problème vient surtout du jeu de données

J'ai rajouter l'index dans la table SAL :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE SAL (
    SALAIRE_ID         INTEGER NOT NULL,
    EMPLOYE_ID         INTEGER,
    EMPLOYE_NOM        VARCHAR(30),
    EMPLOYE_PRENOMS    VARCHAR(30),
    SALAIRE_COTISABLE  NUMERIC(15,2),
    RSS                NUMERIC(15,2),
    JOUR_TRAVAILLER    FLOAT,
    HEURE_TRAVAILLER   FLOAT,
    MOIS               INTEGER,
    TRIMESTRE          INTEGER,
    ANNEE              INTEGER
);

et voici à nouveau le jeu de données:

Code :

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

1
2
3
4
5
6
7
8
9
 
INSERT INTO SAL (SALAIRE_ID, EMPLOYE_ID, EMPLOYE_NOM, EMPLOYE_PRENOMS, SALAIRE_COTISABLE, RSS, JOUR_TRAVAILLER, HEURE_TRAVAILLER, MOIS, TRIMESTRE, ANNEE) 
VALUES (1, 3, 'AAAA', 'BBBB', 25000, 2250, 13, 170, 12, 4, 2017);
INSERT INTO SAL (SALAIRE_ID, EMPLOYE_ID, EMPLOYE_NOM, EMPLOYE_PRENOMS, SALAIRE_COTISABLE, RSS, JOUR_TRAVAILLER, HEURE_TRAVAILLER, MOIS, TRIMESTRE, ANNEE) 
VALUES (2, 2, 'CCCC', 'DDDDD', 19000, 1710, 21, 165, 12, 4, 2017);
INSERT INTO SAL (SALAIRE_ID, EMPLOYE_ID, EMPLOYE_NOM, EMPLOYE_PRENOMS, SALAIRE_COTISABLE, RSS, JOUR_TRAVAILLER, HEURE_TRAVAILLER, MOIS, TRIMESTRE, ANNEE) 
VALUES (3, 1, 'EEEE', 'FFFF', 23500, 2115, 22, 170, 12, 4, 2017);
INSERT INTO SAL (SALAIRE_ID, EMPLOYE_ID, EMPLOYE_NOM, EMPLOYE_PRENOMS, SALAIRE_COTISABLE, RSS, JOUR_TRAVAILLER, HEURE_TRAVAILLER, MOIS, TRIMESTRE, ANNEE) 
VALUES (4, 1, 'EEEE', 'FFFF', 20000, 1800, 20, 140, 12, 4, 2017);

En tout cas c'est toujours la même chose.

**freud** · 12/10/2018, 21h11

Avec un DISTINCT ca régle seulement le probleme du code poster:

Code :

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

1
2
3
4
5
6
7
 
SELECT DISTINCT C.EMPLOYE_ID,S.EMPLOYE_NOM,S.EMPLOYE_PRENOMS,C.ASSIETTE,C.COTISATION,C.NB_JOUR,C.NB_HEURE
FROM COTISATION C INNER JOIN SALAIRES S ON C.EMPLOYE_ID=S.EMPLOYE_ID
WHERE MOIS=:MOIS AND ANNEE=:ANNEE
INTO
 
:EMPLOYE_ID,:EMPLOYE_NOM,:EMPLOYE_PRENOMS,:ASSIETTE,:COTISATION,:JOUR_TRAVAILLER,:HEURE_TRAVAILLER

Le problème se reproduira dans un autre contexte avec des champs supplémentaires.
Peut-être bien qu'il y a un moyen d'utiliser la CTE COTISATION qui me renvoi correctement les données en la jointant avec la table SALAIRES ?????

**SergioMaster** · 13/10/2018, 07h25

Bonjour,
Vous n'avez pas tenu compte de ma remarque

Envoyé par SergioMaster

ce qui l'est moins c'est la CTE SALAIRE qui elle n'est pas groupée (si le doublon est accepté bien entendu)

Voilà la chose que j'entendais par là

Code :

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

SALAIRE AS (SELECT ID_EMPLOYE,MAX(NOM) NOM,MAX(PRENOM) PRENOM FROM SALAIRES WHERE ... GROUP BY 1

même si de mon point de vue elle n'a pas lieu d'être , trop simplifier n'est pas toujours adéquat la structure de la BDD s'en ressent et "horribilise" la chose

dans votre jeu d'essai si vous avez rajouté SALAIRE_ID comme clé mais cela ne change rien le problème c'est que vous avez pour un même salarié pour une même période deux SALAIRE_ID et c'est là que ça coince

Soit c'est voulu alors votre critère de jointure doit être SALAIRE_ID et non EMPLOYE_ID et vous ne devez pas grouper
Soit c'est un problème de données !

Les doublons (employe+période) sont possibles

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
WITH
G
AS
(
  SELECT  EMPLOYE_ID,SUM(SALAIRE_COTISABLE) AS ASSIETTE,SUM(RSS) AS COTISATION,
  SUM(JOUR_TRAVAILLER) AS JOUR_TRAVAILLER,SUM(HEURE_TRAVAILLER) AS HEURE_TRAVAILLER
  FROM SALAIRES
  WHERE MOIS=:MOIS AND ANNEE=:ANNEE
  GROUP BY 1
)
SELECT E.EMPLOYE_ID,E.EMPLOYE_NOM,E.EMPLOYE_PRENOMS,G.ASSIETTE,G.COTISATION,G.JOUR_TRAVAILLER,G.HEURE_TRAVAILLER
FROM EMPLOYES E JOIN G ON E.EMPLOYE_ID=G.EMPLOYE_ID

*NOTE si un employé n'a pas travaillé mais que l'on veut obtenir quand même une indication

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
WITH
G
AS
(
  SELECT  EMPLOYE_ID,SUM(SALAIRE_COTISABLE) AS ASSIETTE,SUM(RSS) AS COTISATION,
  SUM(JOUR_TRAVAILLER) AS JOUR_TRAVAILLER,SUM(HEURE_TRAVAILLER) AS HEURE_TRAVAILLER
  FROM SALAIRES
  WHERE MOIS=:MOIS AND ANNEE=:ANNEE
  GROUP BY 1
)
SELECT E.EMPLOYE_ID,E.EMPLOYE_NOM,E.EMPLOYE_PRENOMS,COALESCE(G.ASSIETTE,0),COALESCE(G.COTISATION,0),COALESCE(G.JOUR_TRAVAILLER,0),COALESCE( G.HEURE_TRAVAILLER,0)
FROM EMPLOYES E LEFT JOIN G ON E.EMPLOYE_ID=G.EMPLOYE_ID

**freud** · 13/10/2018, 12h06

Bonjour,

Fallait joindre par la table EMPLOYE pour obtenir le groupage et éviter les doublons où en avais-je la tête :furax:

Et joindre par la table SALAIRES pour avoir les doublons (voulu) parce que un employé peut avoir plusieurs salaires dans le même mois en principe maximum deux. C'est selon le choix de la requête depuis le client 'détailler' ou 'cumuler'.

Envoyé par SergioMaster

Vous n'avez pas tenu compte de ma remarque

Si, mais je suis resté perplexe pour le groupage par des MAX d'ailleurs cela aurait horribiliser la chose :) . Ce n'est pas la structure de la BDD qui fait ressentir ca c'est la duplication d'informations de l'employé se trouvant à la fois dans les tables EMPLOYE et SALAIRES mais c'est fait exprès d'où ma confusion.

Donc fallait joindre par la table EMPLOYE merci beaucoup SergioMaster

Somme champs dans CTE avec lignes dupliquées

SQL Firebird

Discussions similaires

Partager

Partager