CODEWITHSUNDEEP
mysql
Roddeh
Roddeh

Reputation: 207

MYSQL cumulative sum by player

Im having a table with players and their scores. I managed to create a query to get the cumulative sum. I want the query to group this cumulative sum by player, so that the cumulative sum of the previous players is not included to start with the cumulative sum of the new player. An example of the result I get with my current query:

+--------+--------+-------+
| player | total  | cumul |
+--------+--------+-------+
| Arne   |     16 |    16 |
| Arne   |    -48 |   -32 |
| Arne   |     13 |   -19 |
| Arne   |    -17 |   -36 |
| Arne   |      7 |   -29 |
| Arne   |     41 |    12 |
| Arne   |    -30 |   -18 |
| Arne   |     -6 |   -24 |
| Arne   |     18 |    -6 |
| Bjorg  |     -5 |   -11 |
| Bjorg  |      9 |    -2 |
| Bjorg  |    -38 |   -40 |
| Bjorg  |    -12 |   -52 |
| Bjorg  |     11 |   -41 |
| Bjorg  |      3 |   -38 |
+--------+--------+-------+

How it should look like:

 +--------+--------+-------+
 | speler | total  | cumul |
 +--------+--------+-------+
 | Arne   |     16 |    16 |
 | Arne   |    -48 |   -32 |
 | Arne   |     13 |   -19 |
 | Arne   |    -17 |   -36 |
 | Arne   |      7 |   -29 |
 | Arne   |     41 |    12 |
 | Arne   |    -30 |   -18 |
 | Arne   |     -6 |   -24 |
 | Arne   |     18 |    -6 |
 | Bjorg  |     -5 |    -5 |
 | Bjorg  |      9 |     4 |
 | Bjorg  |    -38 |   -34 |
 | Bjorg  |    -12 |   -46 |
 | Bjorg  |     11 |   -35 |
 | Bjorg  |      3 |   -32 |
 +--------+--------+-------+

The problem with the first table is that the second player uses the cumul of the previous player to start from. How should I alter my query in order to switch between players?

Here's my query:

SELECT t.player,
     t.total,
     @running_total := @running_total + t.total AS cumul
FROM ( SELECT player, id, sum(punten) as total FROM `whiteboard_games` 
WHERE 1 group by player, id) t
JOIN (SELECT @running_total := 0) table1
ORDER BY t.player, id

thanks in advance!

Upvotes: 2

Views: 2331

Answers (1)

Meherzad
Meherzad

Reputation: 8553

Try this query

select 
    @score:=if(@prevPlayer = player, @score+total, total) as cumil,
    total, 
    @prevPlayer:=player as player 
from
    (select * from tbl order by player) a join 
    (select @score:=0, @prevPlayer:='') b

Results:

| CUMIL | TOTAL | PLAYER |
--------------------------
|    16 |    16 |   Arne |
|    34 |    18 |   Arne |
|    28 |    -6 |   Arne |
|    -2 |   -30 |   Arne |
|    39 |    41 |   Arne |
|    46 |     7 |   Arne |
|    29 |   -17 |   Arne |
|    42 |    13 |   Arne |
|    -6 |   -48 |   Arne |
|    11 |    11 |  Bjorg |
|    -1 |   -12 |  Bjorg |
|   -39 |   -38 |  Bjorg |
|   -30 |     9 |  Bjorg |
|   -35 |    -5 |  Bjorg |
|   -32 |     3 |  Bjorg |

FIDDLE

Upvotes: 3

Related Questions