How do I get a "running sum" or running total of team scores in SQL?

See more:

SQL

SqlDataSource

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
/* WHERE Week < 4 and TeamNum < 4 */
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.