CODEWITHSUNDEEP
mysqlsql
PRO_gramista
PRO_gramista

Reputation: 922

How to make difference of two sum queries

I have to calculate goals difference of soccer teams. basically it is: (goalsScoredAtHome + goalsScoredAway) - (goalsConciededAtHome + goalsConciededAway) Everything is saved in one table:

homeTeam | awayTeam | goalsHome | goalsAway
  USA    |  Poland  |     2     |    0
 Poland  |   USA    |     3     |    1

this is what I have as 4 separate queries:

(select sum(goalsHome) as GoalsScoredHome from game where home = 'USA' 
+
select sum(goalsAway) as GoalsScoredAway from game where away = 'USA')
-
(select sum(goalsAway) as GoalsConciededHome from game where home = 'USA'
+
select sum(goalsHome) as GoalsConciededAway from game where away = 'USA')

Is there any way to do it in one query?

Upvotes: 1

Views: 78

Answers (2)

user3470953
user3470953

Reputation: 11335

You can do like the following 

select sum(gs-gs) as GoalScore from(
  select sum(GoalsScore1) as gs from (
    select sum(goalsHome) as GoalsScore1 from game where homeTeam = 'USA' union all select sum(goalsAway) as GoalsScore1 from game where awayTeam = 'USA'
  )gs1 union all
  select sum(GoalsScore2) as gs from (
    select sum(goalsHome) as GoalsScore2 from game where homeTeam = 'USA' union all select sum(goalsAway) as GoalsScore2 from game where awayTeam = 'USA'
  )gs2
)gs3

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269923

A direct translation of your query uses conditional aggregation:

select (sum(case when home = 'USA' then goalsHome else 0 end) +
        sum(case when away = 'USA' then goalsAway else 0 end)
       ) -
       (sum(case when home = 'USA' then goalsAway else 0 end) +
        sum(case when away = 'USA' then goalsHome else 0 end)
       )
from game;

You can simplify this to:

select (sum(case when home = 'USA' then goalsHome - goalsAway else 0 end) +
        sum(case when away = 'USA' then goalsAway - goalsHome else 0 end)
       )
from game;

Upvotes: 5

Related Questions