CODEWITHSUNDEEP
sql
TaylorM
TaylorM

Reputation: 109

Tracking 'streaks' by iteration sql

I currently have a query for a table that currently calculates an artificial ranking based on previous win's and losses. For a WIN a team is credited +1 while a LOSS incurs a -1 for a team.

As of right now i currently have it figuring the final tally by team but what I am looking for is to track the rise and fall of the streak.

Here is the link to the http://sqlfiddle.com/#!2/d87d8/1SQLFIDDLE

Here is what I am looking for:

 Team     |    Streak
 'Team A'      0
 'Team B'      0
 'Team C'      0
 'Team D'      0

 'Team A'      1
 'Team B'      -1
 'Team C'      1
 'Team D'      -1

 'Team A'      2
 'Team B'      -2
 'Team C'      0
 'Team D'      0

 'Team A'      1
 'Team B'      -3
 'Team C'      1
 'Team D'      1

SOLVED

SELECT teamName, (
SELECT sum(CASE matchVictory WHEN 1 then 1 else -1 end)  
FROM poolOfTeams b 
WHERE b.gameId<=temp.gameId 
AND b.teamName=temp.teamName) as Streak 
FROM temp
GROUP BY teamName, gameId
ORDER BY teamName, gameId;

Using the select statement provided by Dwayne Towell, I only had to insert a case statement.

Upvotes: 0

Views: 70

Answers (1)

Dwayne Towell
Dwayne Towell

Reputation: 8613

Try something along these lines:

SELECT gameId, teamName, (
    SELECT sum(matchVictory)  
    FROM temp b 
    WHERE b.gameId<=temp.gameId 
    AND b.teamName=temp.teamName) as Streak 
FROM temp 
GROUP BY teamName, gameId
ORDER BY gameId, teamName;

This assumes victory/lose is indicated by 1/-1 respectively. The order is not exactly as you intended but maybe you can work it out from here.

There is probably a way to do this with window functions but you didn't

Upvotes: 1

Related Questions