0
votes

sql plusieurs comptes

J'ai deux requêtes qui effectuent des comptages. L'un est un décompte total et le suivant est un décompte total pour le nombre de cas de test qui ont réussi.

Je voudrais écrire une requête pour cela (avoir un jeu de résultats). Comment est-il possible que je puisse renvoyer une table qui renvoie ce qui suit

|Release Name| Total Count | Passed


SELECT
REL.REL_NAME as "Release Name",
count(REL.REL_NAME) as "Total Count"
FROM TEST TST
INNER JOIN TESTCYCL TCY ON TST.TS_TEST_ID = TCY.TC_TEST_ID
INNER JOIN CYCLE CYC ON TCY.TC_CYCLE_ID = CYC.CY_CYCLE_ID
INNER JOIN RELEASE_CYCLES RCY ON CYC.CY_ASSIGN_RCYC = RCY.RCYC_ID
INNER JOIN RELEASES REL ON REL.REL_ID = RCY.RCYC_PARENT_ID
GROUP BY REL.REL_NAME
ORDER BY REL.REL_NAME

SELECT
REL.REL_NAME as "Release Name",
count(REL.REL_NAME) as "Passed"
FROM TEST TST
INNER JOIN TESTCYCL TCY ON TST.TS_TEST_ID = TCY.TC_TEST_ID
INNER JOIN CYCLE CYC ON TCY.TC_CYCLE_ID = CYC.CY_CYCLE_ID
INNER JOIN RELEASE_CYCLES RCY ON CYC.CY_ASSIGN_RCYC = RCY.RCYC_ID
INNER JOIN RELEASES REL ON REL.REL_ID = RCY.RCYC_PARENT_ID
WHERE TC_STATUS = 'Passed'
GROUP BY REL.REL_NAME
ORDER BY REL.REL_NAME`

sql count

0 commentaires

3 Réponses :

3
votes

Cela vous donnera les deux résultats:

SELECT
REL.REL_NAME as "Release Name",
count(REL.REL_NAME) as "Total Count"
sum(case when TC_STATUS = 'Passed' then 1 else 0 end) as "Passed"
FROM TEST TST
INNER JOIN TESTCYCL TCY ON TST.TS_TEST_ID = TCY.TC_TEST_ID
INNER JOIN CYCLE CYC ON TCY.TC_CYCLE_ID = CYC.CY_CYCLE_ID
INNER JOIN RELEASE_CYCLES RCY ON CYC.CY_ASSIGN_RCYC = RCY.RCYC_ID
INNER JOIN RELEASES REL ON REL.REL_ID = RCY.RCYC_PARENT_ID

GROUP BY REL.REL_NAME
ORDER BY REL.REL_NAME`

0 commentaires

0
votes

avec agrégation conditionnelle:

SELECT
REL.REL_NAME as "Release Name",
COUNT(REL.REL_NAME) as "Total Count",
COUNT(CASE WHEN TC_STATUS = 'Passed' THEN REL.REL_NAME END) as "Passed"
FROM TEST TST
INNER JOIN TESTCYCL TCY ON TST.TS_TEST_ID = TCY.TC_TEST_ID
INNER JOIN CYCLE CYC ON TCY.TC_CYCLE_ID = CYC.CY_CYCLE_ID
INNER JOIN RELEASE_CYCLES RCY ON CYC.CY_ASSIGN_RCYC = RCY.RCYC_ID
INNER JOIN RELEASES REL ON REL.REL_ID = RCY.RCYC_PARENT_ID
GROUP BY REL.REL_NAME
ORDER BY REL.REL_NAME

0 commentaires

0
votes

Je ne sais pas si vous cherchez quelque chose comme ça ..

--EXEMPLES DE TABLEAUX--

select 
rel.Release_Name,
count(rel.Release_Name) as Total_Count,
sum(case when tst.tc_status = 'Pass' then 1 else 0 end) as "Passed"
from rel
inner join tst on rel.tst_id=tst.tst_id
group by rel.release_name
order by rel.release_name;

--EXEMPLES DE DONNÉES--

--Query Used--

create table rel
(
tst_id int,
Release_Name varchar(50)
);


insert into rel values
(1,'Jan_Release'),
(2,'Feb_Release'),
(3,'Mar_Release'),
(2,'Mar_Release'),
(1,'Feb_Release');

create table tst
(
tst_id int,
tc_status varchar(50)
);

insert into tst values
(1,'Pass'),
(2,'Fail'),
(3,'Pass');

--Ensemble de résultats--

0 commentaires