Hello
The below code works for giving me weekly scores for a team of 4 players.
How do I get a running total?
Have tried few ways without success.
EDIT: for anyone following this thread, here is some test data:
(
select
'01' as Week,
4 as TeamNum,
1 as GameNum,
684 as Gm1,
810 as Gm2,
659 as Gm3
union select
'02' as Week,
4 as TeamNum,
2 as GameNum,
667 as Gm1,
810 as Gm2,
659 as Gm3
union select
'03' as Week,
4 as TeamNum,
3 as GameNum,
506 as Gm1,
810 as Gm2,
659 as Gm3
union select
'04' as Week,
4 as TeamNum,
4 as GameNum,
668 as Gm1,
810 as Gm2,
659 as Gm3
union select
'05' as Week,
4 as TeamNum,
5 as GameNum,
688 as Gm1,
810 as Gm2,
659 as Gm3) as testData
Sorry for confusion but previous answer worked partially and did not notice until today.
It gave the result of a running total but did not separate between teams.
When I tried different versions of grouping by "week" or "team" did not work.
Thanks for solutions but I will give some actual sample data and the expected result for clarifications.
1.
table b_Scores
Week TeamNum Gm1 Gm2 Gm3 Total Won Lost
01 1 283 263 323 869 2 1
01 2 798 768 703 2,269 1 2
01 3 599 701 580 1,880 2 1
02 1 446 384 456 1,286 0 3
02 2 748 811 738 2,297 1 2
02 3 550 551 491 1,592 3 0
03 1 405 380 395 1,180 3 0
03 2 683 787 803 2,273 0 3
03 3 564 694 604 1,862 3 0
2.
table b_Matches
MatchWk MatchTm WON LOST
1 1 2 1
1 2 1 2
1 3 2 1
2 1 0 3
2 2 1 2
2 3 3 0
3 1 3 0
3 2 0 3
3 3 3 0
3. Expected Result.
RESULT EXPECTED
Week Team Num Gm1 Gm2 Gm3 Total Rtotal Won Lost Total Wins Total Losses
1 1 283 263 323 869 869 2 1 2 1
1 2 798 768 703 2,269 3,138 1 2 3 3
1 3 599 701 580 1,880 5,018 2 1 5 4
2 1 446 384 456 1,286 1286 0 3 0 3
2 2 748 811 738 2,297 3,583 1 2 1 5
2 3 550 551 491 1,592 5,175 3 0 4 5
3 1 405 380 395 1,180 1180 3 0 3 0
3 2 683 787 803 2,273 3,453 0 3 3 3
3 3 564 694 604 1,862 5,315 3 0 6 3
What I have tried:
<pre>SELECT
Week,
TeamNum ,
Gm1,
Gm2,
Gm3,
Gm1 + Gm2 + Gm3 AS total,
@rt := @rt + Gm1 + Gm2 + Gm3 AS rt
FROM
b_Scores
join (SELECT @rt:=0 ) as dummy
This is my code with previous answer included.
(SELECT 'Week', 'TeamNum', 'Gm1', 'Gm2', 'Gm3','Total', 'rt' ,'Won', 'Lost', 'Ttl Wins', 'Ttl Losses')
UNION
( SELECT
Week,
TeamNum,
FORMAT( SUM(Gm1),0) AS "Gm1",
FORMAT( SUM(Gm2),0) AS "Gm2",
FORMAT( SUM(Gm3),0) AS "Gm3",
Format(SUM(Gm1)+ SUM(Gm2)+ SUM(Gm3),0) as "Total",
@rt := @rt + Gm1 + Gm2 + Gm3 AS rt ,
WON, LOST,
Format(SUM(won),0) as "TtlWins",
Format(SUM(lost),0) as "TtlLosses"
FROM b_Scores R
JOIN (SELECT @rt:=0 ) as dummy
JOIN b_Matches
ON Week = MatchWk AND TeamNum = MatchTm
GROUP BY TeamNum, Week
ORDER BY `TeamNum`,`Week`)
Result:
Week TeamNum Gm1 Gm2 Gm3 Total rt Won Lost Ttl Wins Ttl Losses
1 1 283 263 323 869 418 2 1 4 2
2 1 446 384 456 1,286 5463 0 3 0 9
3 1 405 380 395 1,180 10562 3 0 9 0
1 2 798 768 703 2,269 1459 1 2 4 8
2 2 748 811 738 2,297 6946 1 2 4 8
3 2 683 787 803 2,273 11853 0 3 0 12
1 3 599 701 580 1,880 3516 2 1 8 4
2 3 550 551 491 1,592 8960 3 0 12 0
3 3 564 694 604 1,862 14066 3 0 12 0
In this result had the following errors.
1. rt (running total) did not work when grouped by week and team.
2. Total wins or losses did not calculate properly. Added this portion because had hard time incorporating previous code into my code when i had additional joins.
Hope this additional data helps for clarity.