CODEWITHSUNDEEP
mysqlsqlsum
Isaac Palacio
Isaac Palacio

Reputation: 203

Add results of a query

I have this query:

select 
sum(if(idcasa = 254, gcasa, 0)) as casa,  
sum(if(idvisitante = 254, gvisitante, 0)) as visitante
from partido
where  idcasa = 254 or idvisitante = 254 and  idpais = 1 and idtemporada = 1 and idcategoria = 1;

and what I want is to add the results, something like this:

sum(casa + visitante) as goles

Upvotes: 0

Views: 37

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I suspect this is the query you want:

select sum(case when idcasa = 254 then gcasa else 0 end) as casa,  
       sum(case when idvisitante = 254 then gvisitante else 0 end) as visitante,
       sum(case when idcasa = 254 then gcasa else gvisitante end) as total
from partido
where (idcasa = 254 or idvisitante = 254) and
      idpais = 1 and
      idtemporada = 1 and
      idcategoria = 1;

First, note the change to the where clause -- the parentheses. I am guessing this is the logic your really intend.

Second, it is simple enough to just add the values in an expression. You can use a subquery to define the values first and just add them, but the expression is simple enough. And, subqueries usually incur performance overheads in MySQL.

Third, I replaced the if with case. The latter is the ANSI standard for conditional expressions.

Upvotes: 2

ila
ila

Reputation: 31

WITH temp AS ( select sum(if(idcasa = 254, gcasa, 0)) as casa,
sum(if(idvisitante = 254, gvisitante, 0)) as visitante from partido where idcasa = 254 or idvisitante = 254 and idpais = 1 and idtemporada = 1 and idcategoria = 1 ) SELECT sum(casa + visitante) as goles FROM temp;

Upvotes: 1

Related Questions