CODEWITHSUNDEEP
sql
thenarfer
thenarfer

Reputation: 455

SQL query: sum scores of players

Given the table Games:

Id Player1 Player2 ScorePlayer1 ScorePlayer2
1 Cathrine John 3 0
2 Bob Cathrine 2 3
3 John Bob 3 2

Which SQL query will output:

Player TotalScore
Cathrine 6
Bob 4
John 3

Upvotes: 0

Views: 33

Answers (1)

Stu
Stu

Reputation: 32599

You can agregate and union separately, then aggregate the result:

with p as (
    select player1 Player, Sum(ScorePlayer1) TotalScore
    from Games
    group by player1
    union all
    select player2, Sum(ScorePlayer2) 
    from Games
    group by player2
)
select Player, Sum(TotalScore) TotalScore
from p
group by Player
order by TotalScore desc;

Upvotes: 2

Related Questions