CODEWITHSUNDEEP
mysql
md9845
md9845

Reputation: 11

MySQL Running Total on multiple columns grouped by another column

I'm trying to create running totals on multiple columns based on grouping by another column.

Starting with the approach in the answer here Running total with in each group using MySQL I can get it to work for totaling one column but any additional columns after that calculate a running grand total - the grouping is ignored.

Sample data - games table

|TeamID|GameID| w| l|
|------|------|--|--|
|     1|     1| 1| 0|
|     1|     2| 0| 1|
|     1|     3| 1| 0|
|     1|     9| 0| 1|
|     1|    10| 1| 0|
|     2|     4| 1| 0|
|     2|     5| 1| 0|
|     2|     6| 1| 0|
|     2|     7| 0| 1|
|     2|     8| 0| 1|
|     3|     1| 0| 1|
|     3|     2| 1| 0|
|     3|     3| 0| 1|
|     3|     7| 1| 0|
|     3|     8| 1| 0|
|     4|     4| 0| 1|
|     4|     5| 0| 1|
|     4|     6| 0| 1|
|     4|     9| 1| 0|
|     4|    10| 0| 1|

Desired result - cumulative w and l, grouped by TeamID

|TeamID|GameID| w| l|cum_w|cum_l|   Pct|
|------|------|--|--|-----|-----|------|
|     1|     1| 1| 0|    1|    0| 1.000|
|     1|     2| 0| 1|    1|    1|  .500|
|     1|     3| 1| 0|    2|    1|  .667|
|     1|     9| 0| 1|    2|    2|  .500|
|     1|    10| 1| 0|    3|    2|  .600|
|     2|     4| 1| 0|    1|    0| 1.000|
|     2|     5| 1| 0|    2|    0| 1.000|
|     2|     6| 1| 0|    3|    0| 1.000|
|     2|     7| 0| 1|    3|    1|  .750|
|     2|     8| 0| 1|    3|    2|  .600|
|     3|     1| 0| 1|    0|    1|  .000|
|     3|     2| 1| 0|    1|    1|  .500|
|     3|     3| 0| 1|    1|    2|  .333|
|     3|     7| 1| 0|    2|    2|  .500|
|     3|     8| 1| 0|    3|    2|  .600|
|     4|     4| 0| 1|    0|    1|  .000|
|     4|     5| 0| 1|    0|    2|  .000|
|     4|     6| 0| 1|    0|    3|  .000|
|     4|     9| 1| 0|    1|    3|  .250|
|     4|    10| 0| 1|    1|    4|  .200|

I modified the code in the link above and am running this... (I'm not even trying to calculate the Pct yet)

    select g.*,
       (@w := if(@TeamID = TeamID, @w + w, 
                 if(@TeamID := TeamID, w, w)
                )
       ) as cum_w, 
       (@l := if(@TeamID = TeamID, @l + l,
                 if(@TeamID := TeamID, l, l)
                )
       ) as cum_l        
from (select TeamID, GameID, w, l
      from games) g
cross join (select @TeamID := -1, @w := 0, @l := 0) params
order by TeamID, GameID;

What I get. Cumulative w works, Cumulative l is a running grand total which ignores the TeamID. If I remove cum_w, cum_l works. If I reverse the order in the select statement the first one works and the second one doesn't

|TeamID|GameID| w| l|cum_w|cum_l|
|------|------|--|--|-----|-----|
|     1|     1| 1| 0|    1|    0|
|     1|     2| 0| 1|    1|    1|
|     1|     3| 1| 0|    2|    1|
|     1|     9| 0| 1|    2|    2|
|     1|    10| 1| 0|    3|    2|
|     2|     4| 1| 0|    1|    2|
|     2|     5| 1| 0|    2|    2|
|     2|     6| 1| 0|    3|    2|
|     2|     7| 0| 1|    3|    3|
|     2|     8| 0| 1|    3|    4|
|     3|     1| 0| 1|    0|    5|
|     3|     2| 1| 0|    1|    5|
|     3|     3| 0| 1|    1|    6|
|     3|     7| 1| 0|    2|    6|
|     3|     8| 1| 0|    3|    6|
|     4|     4| 0| 1|    0|    7|
|     4|     5| 0| 1|    0|    8|
|     4|     6| 0| 1|    0|    9|
|     4|     9| 1| 0|    1|    9|
|     4|    10| 0| 1|    1|   10|

Ultimately, I will have two more columns which have running totals grouped by TeamID, plus running 10 game w & l and pct within that 10 games.

Upvotes: 0

Views: 262

Answers (1)

nbk
nbk

Reputation: 49373

Your user defined variable gets overwritten in the first column. Add a second one for lost

CREATE TABLE games (
  `TeamID` INTEGER,
  `GameID` INTEGER,
  `w` INTEGER,
  `l` INTEGER
);

INSERT INTO games
  (`TeamID`, `GameID`, `w`, `l`)
VALUES
  ('1', '1', '1', '0'),
  ('1', '2', '0', '1'),
  ('1', '3', '1', '0'),
  ('1', '9', '0', '1'),
  ('1', '10', '1', '0'),
  ('2', '4', '1', '0'),
  ('2', '5', '1', '0'),
  ('2', '6', '1', '0'),
  ('2', '7', '0', '1'),
  ('2', '8', '0', '1'),
  ('3', '1', '0', '1'),
  ('3', '2', '1', '0'),
  ('3', '3', '0', '1'),
  ('3', '7', '1', '0'),
  ('3', '8', '1', '0'),
  ('4', '4', '0', '1'),
  ('4', '5', '0', '1'),
  ('4', '6', '0', '1'),
  ('4', '9', '1', '0'),
  ('4', '10', '0', '1');

    select g.*,
       (@w := if(@TeamID = TeamID, @w + w, 
                 if(@TeamID := TeamID, w,w)
                )
       ) as cum_w, 
       (@l := if(@Team2ID = TeamID, @l + l,
                 if(@Team2ID := TeamID, l, l)
                )
       ) as cum_l        
from (select TeamID, GameID, w, l
      from games order by TeamID, GameID) g
cross join (select @TeamID := -1, @Team2ID := -1, @w := 0, @l := 0) params
;

TeamID | GameID |  w |  l | cum_w | cum_l
-----: | -----: | -: | -: | ----: | ----:
     1 |      1 |  1 |  0 |     1 |     0
     1 |      2 |  0 |  1 |     1 |     1
     1 |      3 |  1 |  0 |     2 |     1
     1 |      9 |  0 |  1 |     2 |     2
     1 |     10 |  1 |  0 |     3 |     2
     2 |      4 |  1 |  0 |     1 |     0
     2 |      5 |  1 |  0 |     2 |     0
     2 |      6 |  1 |  0 |     3 |     0
     2 |      7 |  0 |  1 |     3 |     1
     2 |      8 |  0 |  1 |     3 |     2
     3 |      1 |  0 |  1 |     0 |     1
     3 |      2 |  1 |  0 |     1 |     1
     3 |      3 |  0 |  1 |     1 |     2
     3 |      7 |  1 |  0 |     2 |     2
     3 |      8 |  1 |  0 |     3 |     2
     4 |      4 |  0 |  1 |     0 |     1
     4 |      5 |  0 |  1 |     0 |     2
     4 |      6 |  0 |  1 |     0 |     3
     4 |      9 |  1 |  0 |     1 |     3
     4 |     10 |  0 |  1 |     1 |     4

db<>fiddle here

Upvotes: 1

Related Questions