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`

).

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 ...

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

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

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

Thank you very much!

I will give it a try.

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

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