Discussion :

[Lloyd7]

Bonjour à tous,

Ca fait un petit moment que je suis en galère avec mon code SQL. Voici le topo:
Je dois effectuer un rapport entre différentes valeurs.
Pour ce faire, je dois compter un certain nombre de 'test' présents dans différents dossiers. Afin de pouvoir faire ce rapport, il me faut le nombre de 'test' ayant le statut 'Passed' ainsi que le nombre de 'test' n'ayant pas le statut 'Failed' ou 'Blocked'.

Voici les 2 requetes correspondantes :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT CYCL_FOLD.CF_ITEM_NAME as Name,
       CYCL_FOLD.CF_ITEM_ID as ID,
       COUNT(TESTCYCL.TC_CYCLE_ID) as Nombre
 
FROM  CYCL_FOLD, TESTCYCL, CYCLE
 
Where CYCL_FOLD.CF_ITEM_ID  =  CYCLE.CY_FOLDER_ID
 and   TESTCYCL.TC_CYCLE_ID  =  CYCLE.CY_CYCLE_ID
 and  (CYCL_FOLD.CF_ITEM_ID = 3412 or CYCL_FOLD.CF_ITEM_ID = 3329 or CYCL_FOLD.CF_ITEM_ID = 3232)
 and TESTCYCL.TC_STATUS = 'Passed'

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT CYCL_FOLD.CF_ITEM_NAME as Name,
       CYCL_FOLD.CF_ITEM_ID as ID,
       COUNT(TESTCYCL.TC_CYCLE_ID) as Nombre
 
FROM  CYCL_FOLD, TESTCYCL, CYCLE
 
Where CYCL_FOLD.CF_ITEM_ID  =  CYCLE.CY_FOLDER_ID
 and   TESTCYCL.TC_CYCLE_ID  =  CYCLE.CY_CYCLE_ID
 and  (CYCL_FOLD.CF_ITEM_ID = 3412 or CYCL_FOLD.CF_ITEM_ID = 3329 or CYCL_FOLD.CF_ITEM_ID = 3232)
 and NOT (TESTCYCL.TC_STATUS = 'Blocked' or TESTCYCL.TC_STATUS = 'Failed')

J'aimerai pouvoir afficher le rapport de chaque count pour chaque dossier, ainsi que le nom et l'id correspondant.
J'ai essayé un select item_name as name, iten_id as id, (select count1) / (select count2) as rapport, mais bien entendu, les sélections filles se font d'abord et je me retrouve avec un résultat identique pour chaque dossier. Ce qui n'est bien sur pas le bon résultat.

Avez vous des idées?
Merci par avance de votre aide.

[escartefigue]

Bonjour,

Vous pouvez simplifier votre requête en utilisant case...when...end

[Lloyd7]

En effet, j'ai déjà essayé mais je me retrouve coincé au niveau du boolean... Je ne sais pas trop comment l'écrire.
J'aimerai que @Nombre1 ne prenne en compte que le statut 'Passed' et @Nombre2 que les tests ne comportant pas le statut 'Failed' ou'Blocked'.

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
SELECT CYCL_FOLD.CF_ITEM_NAME as Name, 
CYCL_FOLD.CF_ITEM_ID as ID,
((@Nombre1 := COUNT(TESTCYCL.TC_CYCLE_ID)) / (@Nombre2 := COUNT(TESTCYCL.TC_CYCLE_ID)))
        as Avancement,
        CASE
          When @Nombre1 is not null then TESTCYCL.TC_STATUS = 'Passed'
          When @Nombre2 is not null then NOT(TESTCYCL.TC_STATUS = 'Failed' or TESTCYCL.TC_STATUS  = 'Blocked')
          Else 'erreur'
        end
 
FROM CYCL_FOLD, TESTCYCL, CYCLE
 
WHERE CYCL_FOLD.CF_ITEM_ID  =  CYCLE.CY_FOLDER_ID
 and   TESTCYCL.TC_CYCLE_ID  =  CYCLE.CY_CYCLE_ID
 and  (CYCL_FOLD.CF_ITEM_ID = 3412 or CYCL_FOLD.CF_ITEM_ID = 3329 or CYCL_FOLD.CF_ITEM_ID = 3232);

[escartefigue]

Un truc de ce genre, à tester, je n'ai pas créé les tables pour vérifier

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT FO.CF_ITEM_NAME as Name,
       FO.CF_ITEM_ID   as ID,
       SUM(CASE WHEN TC_STATUS = 'Passed'               THEN 1 ELSE 0 END)  AS NBOK,
       SUM(CASE WHEN TC_STATUS in ('Blocked', 'Failed') THEN 1 ELSE 0 END)  AS NBKO
from CYCLE CY
inner join CYCL_FOLD  FO
   on FO.CF_ITEM_ID  = CY.CY_FOLDER_ID
inner join TESTCYCL   TE
   on TE.TC_CYCLE_ID = CY.CY_CYCLE_ID
where CY.CF_ITEM_ID in (3412, 3329, 3232)
  and TE.TC_STATUS in ('Passed', 'Blocked', 'Failed')

Par contre j'ai normalisé les jointures en les sortant du filtrage

[Lloyd7]

Merci beaucoup de ta réponse.
Petite erreur sur FO.CF_ITEM_ID (et non pas CY.CF_ITEM_ID ) sur le dernier where.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT FO.CF_ITEM_NAME as Name,
       FO.CF_ITEM_ID   as ID,
       SUM(CASE WHEN TC_STATUS = 'Passed'               THEN 1 ELSE 0 END)  AS NBOK,
       SUM(CASE WHEN TC_STATUS in ('Blocked', 'Failed') THEN 1 ELSE 0 END)  AS NBKO
from CYCLE CY
inner join CYCL_FOLD  FO
   on FO.CF_ITEM_ID  = CY.CY_FOLDER_ID
inner join TESTCYCL   TE
   on TE.TC_CYCLE_ID = CY.CY_CYCLE_ID
where FO.CF_ITEM_ID in (3412, 3329, 3232)
  and TE.TC_STATUS in ('Passed', 'Blocked', 'Failed')

Il me renvoie une erreur comme quoi "la fonction de groupe ne porte pas sur un groupe simple".
Un simple GROUP BY règle le problème

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT FO.CF_ITEM_NAME as Name,
       FO.CF_ITEM_ID   as ID,
       SUM(CASE WHEN TC_STATUS = 'Passed'               THEN 1 ELSE 0 END)  AS NBOK,
       SUM(CASE WHEN TC_STATUS in ('Blocked', 'Failed') THEN 1 ELSE 0 END)  AS NBKO
from CYCLE CY
inner join CYCL_FOLD  FO
   on FO.CF_ITEM_ID  = CY.CY_FOLDER_ID
inner join TESTCYCL   TE
   on TE.TC_CYCLE_ID = CY.CY_CYCLE_ID
where FO.CF_ITEM_ID in (3412, 3329, 3232)
  and TE.TC_STATUS in ('Passed', 'Blocked', 'Failed')
 
Group by FO.CF_ITEM_NAME, FO.CF_ITEM_ID

[Lloyd7]

Déjà un gros merci pour la (très) grosse simplification !
Maintenant j'aimerai pouvoir faire le rapport des 2 SUM et l'afficher dans le tableau final (pour en revenir au problème initial )

[SQLpro]

Ou avez vous appris à faire vos jointures ?????

Une solution :

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
WITH T0 AS
(
SELECT C.CF_ITEM_NAME as Name,
       C.CF_ITEM_ID as ID,
       T.TC_STATUS AS Statut,
       COUNT(T.TC_CYCLE_ID) as Nombre
FROM   CYCLE AS C
       JOIN TESTCYCL AS T
            ON T.TC_CYCLE_ID  =  C.CY_CYCLE_ID
       JOIN CYCL_FOLD AS F
            ON F.CF_ITEM_ID  =  C.CY_FOLDER_ID
WHERE  C.CF_ITEM_ID IN (3412, 3329, 3232)
GROUP  BY T.TC_STATUS
)
SELECT SUM(Nombre) OVER() AS NOMBRE_TOTAL,
       SUM(CASE WHEN Statut = 'Passed' THEN Nombre ELSE 0 END) AS NB_Passed,
       SUM(CASE WHEN Statut = 'Blocked' THEN Nombre ELSE 0 END) AS NB_Blocked,
       SUM(CASE WHEN Statut = 'Failed' THEN Nombre ELSE 0 END) AS NB_Failed,
       SUM(Nombre) - SUM(CASE WHEN Statut = 'Blocked' THEN Nombre ELSE 0 END)
                   - SUM(CASE WHEN Statut = 'Failed' THEN Nombre ELSE 0 END) AS NB_NonFailed_NonBlocked
FROM   T0;

A +

[Lloyd7]

Ou avez vous appris à faire vos jointures ?????

Je n'ai appris que les rudiments du SQL en cours

Un grand merci à vous 2, ca marche nickel

[escartefigue]

Merci beaucoup de ta réponse.
Petite erreur sur FO.CF_ITEM_ID (et non pas CY.CF_ITEM_ID ) sur le dernier where.

Aucune importance, comme il s'agit d'une jointure inner et que le critère de jointure est justement l'ID, vous pouvez utiliser celui de l'une des 3 tables indifféremment

Il me renvoie une erreur comme quoi "la fonction de groupe ne porte pas sur un groupe simple".
Un simple GROUP BY règle le problème

Mea culpa en effet je n'avais pas testé ni vu cet oubli

Maintenant j'aimerai pouvoir faire le rapport des 2 SUM et l'afficher dans le tableau final (pour en revenir au problème initial )

Le mieux est d'utiliser une CTE si votre SGBD le permet (quel est il ?)
Sinon encapsulez la requete qui fonctionne dans un sub-select puis faites votre calcul de %

[Lloyd7]

Le mieux est d'utiliser une CTE si votre SGBD le permet (quel est il ?)
Sinon encapsulez la requete qui fonctionne dans un sub-select puis faites votre calcul de %

La CTE marche très bien