SQL getting SUM of row for each PlayerID

Question

I have a layout that looks something like this:

Player:
PlayerID - PlayerName

Exp
ExpID - PlayerID - ExpChange

What I am trying to do is get the sum of ExpChange for every playername and order it by the sum of expchange. (A high score list with SUM(ExpChange) as the score.

This is what I have so far:

SELECT Player.PlayerName, Exp.ExpChange
FROM Player
INNER JOIN Exp
ON Player.PlayerID=Exp.PlayerID
ORDER BY Exp.ExpChange;

But now I need to somehow sort it per player by the sum of expchange for that player. Is this possible? I tried searching around a ton for it but couldnt find anything. Thanks

P.S. I also tried something like this:

SELECT Player.PlayerName, SUM(Exp.ExpChange) AS exp
FROM Player
INNER JOIN Exp
ON Player.PlayerID=Exp.PlayerID
ORDER BY exp;

but it just outputs the total ExpChange for everyone, not each individual player.

Answer 1

You need to add a GROUP BY clause to get the sum() for each player:

SELECT Player.PlayerName, SUM(COALESCE(Exp.ExpChange, 0)) AS exp
FROM Player
LEFT JOIN Exp
   ON Player.PlayerID=Exp.PlayerID
GROUP BY Player.PlayerName
ORDER BY exp;

If you only want the records that have a match in the exp table, then you can use an INNER JOIN:

SELECT Player.PlayerName, SUM(Exp.ExpChange) AS exp
FROM Player
INNER JOIN Exp
   ON Player.PlayerID=Exp.PlayerID
GROUP BY Player.PlayerName
ORDER BY exp;