Doubt about Querys

Question

Doubt about Querys

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I'm trying to create a view to return the query from a Query. My problem is that I wanted it to return values for each product and it 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. It is consolidating the total and placing for 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 it is bringing the Progress field broken by several decimal places, I wanted it to bring in whole. For example 72.2222222 = 72. I looked at the forum and could not solve my doubts. Can anyone point me a way forward?

sql sql-server

asked by anonymous 08.01.2018 / 13:26

2 answers

1

rLinhares, try the following:

Place in each where of the sub-selects 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 a note to tell you about the performance of this select. This large number of sub-queries brings a certain impact to the query, if you run the query too much, I advise you to change the way of query.

08.01.2018 / 14:07

Create a select of cities, when to choose the city goes to the specific site of the chosen city My pull-right class is not pushing the menu right Can someone help me?

score 0 · Accepted Answer

The error is that in internal queries you do not consider the id of the item, so it always returns the total. I think 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