Doubt about Querys

Question

I am trying to create a view to return the query of a Query. My problem is that I wanted to return values for each product and is returning the same value for the products.

SELECT B.ID_Caso, B.Nome_Caso_Teste AS PRODUTO,
(SELECT COUNT(Status_Execucao) AS Expr1
FROM dbo.Execucao
WHERE (Status_Execucao = 'PASSED')) AS PASSED,
(SELECT COUNT(Status_Execucao) AS Expr1
FROM dbo.Execucao AS Execucao_3
WHERE (Status_Execucao = 'FAILED')) AS FAILED,
(SELECT CAST(COUNT(Status_Execucao) AS float) AS
Expr1
FROM dbo.Execucao AS Execucao_2
WHERE (Status_Execucao = 'PASSED')) /
(SELECT COUNT(Status_Execucao) AS Expr1
FROM dbo.Execucao AS Execucao_1) * 100 AS
PROGRESSO
FROM dbo.Execucao AS A INNER JOIN
dbo.CasoTeste AS B ON A.ID_Caso = B.ID_Caso
GROUP BY B.Nome_Caso_Teste, B.ID_Caso 

And it returns that way, with equal values for the products, and they have different data. Is consolidating the total and putting to the products.

ID_Caso PRODUTO         PASSED FAILED PROGRESSO %
    4    Condominio        13      5       72,2222222222222
    3    Empresarial       13      5       72,2222222222222
    2    Fiança Locatícia  13      5       72,2222222222222
    1    Residenciais      13      5       72,2222222222222

Another problem is that he is bringing the Progress field broken by several decimal places, wanted it to bring in whole. For example 72,222222 = 72. I looked at the forum and could not solve my doubt. Someone can show me a way to follow?

Answer 1

rLinhares try as follows:

Put in each sub-select’s Where the clause

     AND ID_Caso = A.ID_Caso 

to make the filter correctly.

For rounding, you can use the FLOOR function (always round down) as follows:

FLOOR((SELECT CAST(COUNT(Status_Execucao) AS float) AS Expr1
FROM dbo.Execucao AS Execucao_2
WHERE (Status_Execucao = 'PASSED')) /
(SELECT COUNT(Status_Execucao) AS Expr1
FROM dbo.Execucao AS Execucao_1) * 100) AS
PROGRESSO

I have an observation to make to you, regarding the performance of this select. This large amount of sub-querys brings a certain impact to the query, if you perform the query excessively, I advise you to change the form of query.

Answer 2

The error is that in internal queries you do not consider the item id, so it always returns the total. I believe this should solve the problem:

SELECT B.ID_Caso, B.Nome_Caso_Teste AS PRODUTO,
 (SELECT COUNT(Status_Execucao) AS Expr1 FROM dbo.Execucao AS Execucao_4 WHERE (Status_Execucao = 'PASSED') and B.ID_Caso = Execucao_4.ID_Caso) AS PASSED,
 (SELECT COUNT(Status_Execucao) AS Expr1 FROM dbo.Execucao AS Execucao_3 WHERE (Status_Execucao = 'FAILED') and B.ID_Caso = Execucao_3.ID_Caso) AS FAILED,
 (cast(SELECT CAST(COUNT(Status_Execucao) AS float) AS Expr1 FROM dbo.Execucao AS Execucao_2 WHERE Status_Execucao = 'PASSED' and B.ID_Caso = Execucao_2.ID_Caso) / 
   (SELECT COUNT(Status_Execucao) AS Expr1 FROM dbo.Execucao AS Execucao_1) * 100 as numeric(15,0)) AS PROGRESSO
FROM dbo.Execucao AS A INNER JOIN dbo.CasoTeste AS B ON A.ID_Caso = B.ID_Caso
GROUP BY B.Nome_Caso_Teste, B.ID_Caso