Add and split results in the same query

Asked

Viewed 967 times

2

How do I fix 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 values summed in "AS" in the same query

mysql

2 answers

1

To do this, you will need to use subselects, I made 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 ran a test on Sqlfiddle for better understanding

1

Good morning Diego,

I would separate to not give problem with aggregation, because at first we think of Subquery, but I think so can arrive at 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;

  • That wouldn’t be in sqlserver ?

  • David, you’re right, let me tidy up. Thank you!

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.