How to query with Count

Question

I have the table Upshot with thousands of data, Table Result

    NOME_CASA,   NOME_OPONENTE, RESULTADO_FINAL, NOTA_FINAL.
    timeAzul     timePreto         1             2 
    timeAzul     timeBranco        2             2.10 
    timeBranco   timeAzul          3             2.20
    timeCinza    timeAzul          3             1.7  
    timeverde    timePreto         1             1.92 
    timePreto    timeRosa          2             2.05 
    timeAzul     timeBranco        1             2.08 

Above I showed only a summary of the columns and data in the table, observe that the data can be repeated, for better understanding of the question divided into 2 query that will help us in my doubt:

QUERY 1- Return all the team of the house, that the final note is between 2 and 2.20.

SELECT NOME_CASA FROM Resultado where  NOTA_FINAL between 2 and 2.20 

QUERY 2 - Return all home team, with final score between 2 and 2.20, and filter the final result equal to 1.

   SELECT NOME_CASA FROM Resultado where  NOTA_FINAL between 2 and 2.20 and RESULTADO_FINAL = 1 ;

Look what I did in the whole table. Now, I need to get all NOME_CASA that apply the two filters I mentioned for each specific team, ie return the names of the teams where playing with nota_final 2 and 2.20, 60% of the time or more end with the result_final =1; In other words, I would test this condition for each team, and return their name.

Summarizing I need the names of the teams(NOME_CASA) without duplicating, that 60% of the times end with the resultado_final = 1, when the NOTA_FINALbetween 2 and 2.20.

Using the data I posted as an example, only the timeAzul would return in the query I need, because in a total of 3 games of timeAzul, 3 met Query1 and 2 of them ended with the result_final = 1. So (2 * 100) / 3 = 66.6%

How to mount this query?

Answer 1

I read your question a few dozen times to understand your problem, I hope you’re on the right track.

Using the clause count(*) got the amount of records for each query, and in the clause HAVING I created the percentage logic % of which he commented in his question.

SELECT NOME_CASA AS NOME_CASA_COLUNA, 
       count(*) as qtd_query2 
FROM Resultado
       WHERE NOTA_FINAL between 2 and 2.20 and RESULTADO_FINAL = 1
GROUP BY NOME_CASA
HAVING ((qtd_query2 * 100) / 
    ( SELECT count(*) AS qtd_query1 
        FROM Resultado
        WHERE NOTA_FINAL between 2 and 2.20 AND NOME_CASA = NOME_CASA_COLUNA
     GROUP BY NOME_CASA HAVING qtd_query1 > 1)) > 60;   

In that query applied the Query2 as an external query and then do a validation with the total amount of records of Query2 and check if it is equivalent to 60% or more than the amount of records returned in the Query1.

Answer 2

See also on fiddle.

SELECT nome_casa, 
       COUNT(CASE WHEN resultado_final = 1 THEN 1 
                  ELSE NULL 
             END) / COUNT(*) AS percentual
FROM Resultado
WHERE nota_final BETWEEN 2.00 AND 2.20
GROUP BY nome_casa
HAVING percentual >= 0.60;

Answer 3

From what I understand, you want to use COUNT to mount this query ((Query2 * 100) / QUERY2) and get a numerical result. Try doing so:

((SELECT COUNT(nome_casa) FROM resultado WHERE nota_final BETWEEN 2 AND 2.20) * 100) / (SELECT COUNT(nome_casa) FROM resultado WHERE nota_final BETWEEN 2 AND 2.20 AND resultado_final = 1)