Add and split results in the same query

2

How do I resolve this situation?

$q = mysql_query("SELECT *, COUNT(*) as total,
SUM(bolas_agarradas+bolas_espalmadas) as defesas,
SUM(bolas_agarradas+bolas_espalmadas+gols_sofridos) as chances,
SUM(defesas / chances) as porcentual ...

Use the values added in the "AS" in the same query

    
asked by anonymous 17.07.2017 / 14:48

2 answers

1

To do this, you'll have to use subselects , I've done an example, using the fields you use, only to demonstrate:

SELECT COUNT(*) AS total
       ,defesas
       ,chances
       ,SUM(defesas / chances) AS porcentuals   
  FROM (SELECT SUM(bolas_agarradas + bolas_espalmadas) AS defesas
              ,SUM(bolas_agarradas + bolas_espalmadas + gols_sofridos) AS chances
          FROM (SELECT 10 AS bolas_agarradas
                      ,2  AS bolas_espalmadas
                      ,20 AS gols_sofridos
                  FROM dual))
GROUP BY defesas,chances

I tested SQL SQLFiddle for better understanding.

    
17.07.2017 / 15:10
1

Good morning Diego,

I would separate so as not to give a problem with aggregation, because at first we think of Subquery, but I think this way it can reach the same result.

I hope it helps!

DECLARE @Total INTEGER = 0;
DECLARE @Defesa INTEGER = 0;
DECLARE @Chance INTEGER = 0;
DECLARE @Percentual DECIMAL;

SET @Total = (SELECT Count(id) total from teste);
SET @Defesa = (SELECT SUM(bolas_agarradas+bolas_espalmadas) defesa from teste);
SET @Chance = (SELECT SUM(bolas_agarradas+bolas_espalmadas+gols_sofridos) chance from teste)  ;

IF (@Chance > 0)
SET @Percentual = @Defesa/ @Chance;
-- Mostra seu resultado
SELECT @Total as Total, @Defesa as Defesa, @Chance as Chance;
    
17.07.2017 / 15:43