msaz87 Posted March 4, 2010 Share Posted March 4, 2010 Hey all, I was hoping to see if anyone had an idea on how to go about creating a query that would join two tables and develop a "streak" of the same occurrence. The best example for what I mean is basically in any sporting event, if a team wins four games in a row, it would be a streak of four, lost two in a row, streak of two losses, etc. The two tables are as such: The below defines the game, what week it is and the two teams (team_1 and team_2) The second table defines the score and who won or lost (loose is lost, original programmer spoke poor English) This task would require the two tables joined and then somehow counting from the highest (most recent) week backwards, taking the most recent result (1 = win, 0 = loss) and then counting the amount of times it's the same until it switches... so in the images above, using team_id 15 as an example, starting at week 6, the streak is "Won 1". If you started with week 4, it would be "Won 2" and so on... Any help or guidance for how this could be accomplished (if it can be with the current table structure) would be greatly appreciated... Thanks in advance! Link to comment https://forums.phpfreaks.com/topic/194113-using-a-query-and-joining-tables-to-create-a-streak-count/ Share on other sites More sharing options...
fenway Posted March 4, 2010 Share Posted March 4, 2010 This is basically a variant on counting rows based a given column value -- you'll have to use user variables. Link to comment https://forums.phpfreaks.com/topic/194113-using-a-query-and-joining-tables-to-create-a-streak-count/#findComment-1021465 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.