Running Total for Team

Transact-SQL
1

Looking for a running total of Points, Wins and Losses.
My previous attempt gave running total but did not separate by team.
Presently the running totals (rt and wins, losses) do not work.

I am using pre 2012 mysql

(SELECT 'Week', 'TeamNum', 'Gm1', 'Gm2', 'Gm3','Total', 'rt' ,'Won', 'Lost', 'Wins', '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
ORDER BY TeamNum,Week).

2

SELECT
SUM(runningtotalcolum) OVER (PARTITION BY whatever_you_want_to_restart_the_running_total_with ORDER BY whatever_you_want_to_order_by
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM ...

3

Thanks for help.
Sorry but this is over my head, just starting out.

Is this what you mean?

SELECT
(SUM(Gm1)+ SUM(Gm2)+ SUM(Gm3)) OVER (PARTITION BY WEEK, TeamNum)
ORDER BY TeamNum,Week
FROM b_Scores

4

SELECT
SUM(Gm1+ Gm2+ Gm3) OVER (PARTITION BY WEEK, TeamNum ORDER BY TeamNum,Week ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM b_Scores

5

Please post consumable data in the form of a create table statement or declare table variable with insert statements.

6

Thank you very much!
I will give it a try.

7

does this work with pre 2012 Mysql?
I have some errors.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY WEEK, TeamNum ORDER BY TeamNum,Week ROWS BETWEEN UNBOUNDED PRECEDI' at line 2

8

This is a SQL Server forum. The answers here reflect that. the syntax will work in SQL Server 2012 +