Good night. I got this problem, I tried sub querys and case with no success.
When a player kill someone in my game, a new record is added to database, this record contains the ID of the killer and the ID of the killed player, so after a while table will look like this:
killer_id | death_id
100 | 200 -> (Player 100 Killed player 200)
100 | 200
100 | 200
200 | 100
300 | 100
300 | 200
Im trying to get the score of every player, based on their kills minus their deaths, so based on the above data:
Rank:
Pos 1. player 300 / 2 Points (2 Kills - 0 deaths)
Pos 2. player 100 / 1 Point (3 Kills - 2 deaths)
Pos 3. player 200 / -3 Points (1 Kills - 4 deaths)
I want to do this on one SQL query, but I'm stuck... any tip would be appreciated.
You could use union all and aggregation:
select player_id, sum(cnt) score
from (
select killer_id player_id, 1 cnt from mytable
union all select death_id, -1 from mytable
) t
group by player_id
order by score desc
If you also want the player rank, you can use window functions (this requires MySQL 8.0):
select rank() over(order by sum(cnt) desc) pos, player_id, sum(cnt) score
from (
select killer_id player_id, 1 cnt from mytable
union all select death_id, -1 from mytable
) t
group by player_id
order by score desc