CODEWITHSUNDEEP
mysqlcountphpmyadmin
Chris
Chris

Reputation: 115

MySQL count wins/losses to find a streak

I have a table with sport games containing date, teams, and results. I want to find the winning or loosing streak a team is currently having.

My table looks like this:

+------------+----------+---------+---------------+------+------------+
| date_month | date_day | visitor | visitor_score | home | home_score |
+------------+----------+---------+---------------+------+------------+
| May        | 1        | MyTeam  | 90            | Z    | 100        |
+------------+----------+---------+---------------+------+------------+
| April      | 26       | X       | 100           |MyTeam| 90         |
+------------+----------+---------+---------------+------+------------+
| April      | 21       | Y       | 90            |MyTeam| 100        |
+------------+----------+---------+---------------+------+------------+
| March      | 25       | MyTeam  | 90            | W    | 100        |
+------------+----------+---------+---------------+------+------------+

I already accomplished to order the result by date.

SELECT * FROM Schedule WHERE visitor_score>0
            ORDER BY CASE
            when `date_month` = 'May' then 1
            when `date_month` = 'April' then 2
            when `date_month` = 'March' then 3
            else 4
            end asc, date_day desc

The question is, how to find the streak of a team? Myteam lost the last two games, so it should be 0 Wins and 2 Losses. But if it wins the next game, it obviously should be 1Wins and 0 Losses.

I know how to extract the wins and losses (as below) but this is the point I'm stuck at.

WHERE (visitor = 'MyTeam' && visitor_score>home_score) OR (home = 'MyTeam' && home_score>visitor_score)

Upvotes: 0

Views: 153

Answers (1)

desoss
desoss

Reputation: 622

I'm not going to complain your table structure. I assume that you cannot change your table structure.

I already accomplished to order the result by date.

Your solution is pretty ugly. Consider using something like:

SELECT STR_TO_DATE(CONCAT(date_month, date_day, YEAR(CURDATE())),"%M%d%Y") AS date

Notice that I had to use YEAR(CURDATE()) since the year is mandatory in STR_TO_DATE Function and it seems that you are not storing the year of the games. Now you can simply:ORDER BY date DESC

Now I'm going to explain the first solution that came in my mind regarding the W/L/D (Win, Lose, Draw) streak.

Declare 3 functions:

  1. retrieveLastResult (returns W,L,D)
  2. retrieveLastGameDate (return date_month, date_day)
  3. retrieveFirstResultDateDifferentFromLastOne (returns the date of the first game with a result different from the last game)

Below there is a mock for the body of each one of these function.

Once you have those function you can simply do something like this:

SELECT COUNT(*), retrieveLastResult 
FROM Schedule
WHERE
STR_TO_DATE(CONCAT(date_month, date_day, YEAR(CURDATE())),"%M%d%Y") 
    BETWEEN DATE_SUB(retrieveFirstResultDateDifferentFromLastOne(retrieveLastResult,retrieveLastGameDate),INTERVAL 1 DAY) 
       AND retrieveLastGameDate()

retrieveLastResult Function

SELECT 
        IF(`home` = "MyTeam",  
            CASE 
                WHEN  `home_score` > `visitor_score` THEN "W"
                WHEN  `home_score` < `visitor_score` THEN "L"
                WHEN  `home_score` = `visitor_score` THEN "D"
            END,
            CASE 
                WHEN  `visitor_score` > `home_score` THEN "W"
                WHEN  `visitor_score` < `home_score` THEN "L"
                WHEN  `visitor_score` = `home_score` THEN "D"
            END
         )
FROM schedule
ORDER BY STR_TO_DATE(CONCAT(date_month, date_day, YEAR(CURDATE())),"%M%d%Y") DESC
LIMIT 1

retrieveLastGameDate Function

SELECT STR_TO_DATE(CONCAT(date_month, date_day, YEAR(CURDATE())),"%M%d%Y") as date
FROM schedule
ORDER BY date
LIMIT 1

retrieveFirstResultDateDifferentFromLastOne Function

SELECT date
FROM schedule
WHERE 
    IF(`home` = "MyTeam",  
            CASE 
                WHEN  `home_score` > `visitor_score` THEN "W"
                WHEN  `home_score` < `visitor_score` THEN "L"
                WHEN  `home_score` = `visitor_score` THEN "D"
            END,
            CASE 
                WHEN  `visitor_score` > `home_score` THEN "W"
                WHEN  `visitor_score` < `home_score` THEN "L"
                WHEN  `visitor_score` = `home_score` THEN "D"
            END
        ) != lastResult()
ORDER BY STR_TO_DATE(CONCAT(date_month, date_day, YEAR(CURDATE())),"%M%d%Y") DESC
LIMIT 1

Upvotes: 1

Related Questions