Jump to content

mysql streak


Destramic

Recommended Posts

hey guys i have a table called match_results and looks like this

 

match_results

--------------------------

league_result_id

league_match_id

team_id

result

-------------------------

 

now what i want to be able to do is when selecting the results is get the steak where result = win

 

if anyone could help or have a site on how this can be done please...thank you

Link to comment
Share on other sites

sorry about the delay...

 

here is the query i have and i want to add a streak column to it so if you can help or advise me what to do...i would like to add it to this query really if possible

 

SET @rank := 0;
SET @points := 0;
SET @drawing := 0;

SELECT *,
                 @rank := IF(@points = points, IF(@points = '0', @rank + 1, @rank), @rank + 1), 
                         @points := points,
                         @drawing := IF(@points = points, IF(@points = '0', @drawing = '0', @drawing = '1'), @drawing = '0'),
                         IF(@drawing = '1', @rank + 1, @rank) as rank,
                         @drawing := '0'
                  FROM(
                  SELECT t.team_id,
                         t.team_name, 
                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(r.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(r.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(r.result='Win', 3, IF(r.result='Draw', 1, IF(r.result='Loss', 0, 0)))) AS `points`
                  FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                  LEFT JOIN leagues l ON l.league_name = 'test'
                  LEFT JOIN games g ON g.game_abbreviation = 'CSS'
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x

Link to comment
Share on other sites

thanks fenway...a very useful site...now ive tried to do what i think is right but the query isnt working at all

 

the error im getting is

#1242 - Subquery returns more than 1 row

 

SET @rank := 0;
SET @points := 0;
SET @tie := 0; 
SET @win_streak := 0;

SELECT *,
                 @rank := IF(@points = points, IF(@points = '0', @rank + 1, @rank), @rank + 1), 
                         @points := points,
                         @tie := IF(@points = points, IF(@points = '0', @tie = '0', @tie = '1'), @tie = '0'),
                         IF(@tie = '1', @rank + 1, @rank) as rank,
                         @tie := '0',
                         @win_streak := '0'
                  FROM(
                  SELECT t.team_id,
                         t.team_name, 
                         
                          (SELECT 
                          IF(lmr.result='Win', @win_streak + 1, IF(lmr.result='Draw', @win_streak = '0', IF(lmr.result='Loss', @win_streak = '0', 0))) AS `win_streak`
                          FROM league_match_results lmr
                          LEFT JOIN league_matches lm ON lm.league_match_id = lmr.league_match_id
                          WHERE lmr.team_id = t.team_id
                          ORDER BY lm.date DESC),

                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(r.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(r.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(r.result='Win', 3, IF(r.result='Draw', 1, IF(r.result='Loss', 0, 0)))) AS `points`
                         FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                  LEFT JOIN leagues l ON l.league_name = 'test'
                  LEFT JOIN games g ON g.game_abbreviation = 'CSS'
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x

 

the query works without

 

    (SELECT 
                          IF(lmr.result='Win', @win_streak + 1, IF(lmr.result='Draw', @win_streak = '0', IF(lmr.result='Loss', @win_streak = '0', 0))) AS `win_streak`
                          FROM league_match_results lmr
                          LEFT JOIN league_matches lm ON lm.league_match_id = lmr.league_match_id
                          WHERE lmr.team_id = t.team_id
                          ORDER BY lm.date DESC),

 

can you please help me

Link to comment
Share on other sites

ok i sort of go in work...but...it returns the team as having 1 streak...but it has no win result if you can help please

 

SELECT MAX( IF( lmr.result = 'Win', @win_streak +1, IF( lmr.result = 'Draw', @win_streak = '0', IF( lmr.result = 'Loss', @win_streak = '0', @win_streak = '0' ) ) ) ) AS `win_streak`
FROM league_match_results lmr
LEFT JOIN league_matches lm ON lm.league_match_id = lmr.league_match_id
WHERE lmr.team_id =1
ORDER BY lm.date DESC

Link to comment
Share on other sites

ok ive got it working now finnaly...the only problem i do have if you can help is that the win_streak column returns null on some cases...not ive tried to put WHEN NULL THEN 0 in the case but that doesnt do nothing...still returns null...thanks for your help fenway

 

SET @rank := 0;
SET @points := 0;
SET @tie := 0; 
SET @win_streak := 0;

SELECT *,
                 @rank := IF(@points = points, IF(@points = '0', @rank + 1, @rank), @rank + 1), 
                         @points := points,
                         @tie := IF(@points = points, IF(@points = '0', @tie = '0', @tie = '1'), @tie = '0'),
                         IF(@tie = '1', @rank + 1, @rank) as rank,
                         @tie := '0'

                  FROM(
                      SELECT t.team_id,
                         t.team_name, 
                (SELECT

                        MAX(@win_streak := CASE lmr.result
WHEN 'Win'
THEN @win_streak +1
ELSE 0
END)

                          FROM league_match_results lmr
                          LEFT JOIN league_matches lm ON lm.league_match_id = lmr.league_match_id
                          WHERE lmr.team_id = t.team_id                       
           ORDER BY lm.date DESC) AS `win_streak`,
                     
                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(r.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(r.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(r.result='Win', 3, IF(r.result='Draw', 1, IF(r.result='Loss', 0, 0)))) AS `points`
                         FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                  LEFT JOIN leagues l ON l.league_name = 'Counter-Strike Europeon Team Death Match'
                  LEFT JOIN games g ON g.game_abbreviation = 'CSS'
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x

Link to comment
Share on other sites

yeah i suppose i could put

 

if (empty($row['win_steak']))
{
     echo "0";
}
else
{
   echo $row['win_streak'];
}

 

but i'd much prefer to be able to do it in the query...and what do you mean by crazy query? haha...i mean how else should i do what i want to do in a query?

Link to comment
Share on other sites

sorry you are gonna have to bear with me and explain a bit more cause im no pro with mysql

 

wrap max()?

aslso i tried IFNULL(@win_streak, 0) but it didnt work.

 

and you want me to get rid of the case?

 

                        MAX(@win_streak := CASE lmr.result
WHEN 'Win'
THEN @win_streak +1
ELSE 0
END) AS `win_streak`

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.