Jump to content

result streak

Destramic

By Destramic
in MySQL Help

 Share

Recommended Posts

Destramic Advanced Member

Destramic

Posted
Posted

Hey guys im trying to get a win/loss and draw streak using the code I've written down below

(@win_streak := IF(lfr1.result='Win', @win_streak +1, @win_streak = 0)),
(@loss_streak := IF(lfr1.result='Loss', @loss_streak +1, @loss_streak = 0)),
(@draw_streak := IF(lfr1.result='Draw', @draw_streak +1, @draw_streak = 0))

The problem is when I add the @loss_streak and @draw_streak lines to the query it then brings up this error

 

 

ERROR 1241 - OPERAND SHOULD CONTAIN 1 COLUMN

 

any help would be grateful thank you

 

Here is the full SQL

SELECT *,
	                 @rank := IF(@points = points, IF(@points = 0, @rank = 0, @rank), @rank +1), 
                         @points = points,
                         IF(@points = points, IF(@points = 0, @tie = 0, @tie = 1), @tie = 0),
                         IF(@tie = 1, @rank +1, @rank) AS `rank`,
                         @tie = 0
                  FROM(
                         SELECT l.league_id,
                         t.team_id,
                         t.team_name,
                         tlm.previous_rank,
                         tlm.rank_movement,
				         tlm.status,
                       (SELECT 
				
				       
						(@win_streak := IF(lfr1.result='Win', @win_streak +1, @win_streak = 0)),
				(@loss_streak := IF(lfr1.result='Loss', @loss_streak +1, @loss_streak = 0)),
				(@draw_streak := IF(lfr1.result='Draw', @draw_streak +1, @draw_streak = 0))
						
				
				
                         FROM league_fixture_results lfr1
                         LEFT JOIN league_fixtures lf ON lf.league_fixture_id = lfr1.league_fixture_id
				        
                         WHERE lfr1.team_id = t.team_id AND lf.league_id = l.league_id                 
                         ORDER BY lfr1.created DESC), 
				         @win_streak AS `win_streak`, @loss_streak AS `loss_streak`, @draw_streak AS `draw_streak`,
				         @win_streak := 0, @loss_streak := 0, @draw_streak := 0,
		
			
                         COUNT(lfr2.league_fixture_result_id) AS 'matches_played',
                         SUM(IF(lfr2.result='Win', 1, 0)) AS `wins`,
                         SUM(IF(lfr2.result='Loss', 1, 0)) AS `losses`,
                         SUM(IF(lfr2.result='Draw', 1, 0)) AS `draws`,
                         SUM(IF(lfr2.result='Win', l.win_points, IF(lfr2.result='Draw', l.draw_points, IF(lfr2.result='Loss', l.loss_points, 0)))) AS `points`,
				         FORMAT(IFNULL(SUM(IF(lfr2.result='Win', 1, 0)) / COUNT(lfr2.league_fixture_result_id), 0), 2) AS `win_ratio`,
						 FORMAT(IFNULL(SUM(IF(lfr2.result='Win', 1, 0)) / COUNT(lfr2.league_fixture_result_id) * 100, 0), 2) AS `win_percentage`
                  FROM teams t

                  LEFT JOIN league_fixture_results lfr2 ON lfr2.team_id = t.team_id
                  LEFT JOIN team_league_mappings tlm ON tlm.team_id = t.team_id
                  JOIN leagues l
                  JOIN games g 

                 WHERE g.game_abbreviation = 'CS' AND
                       l.league_name = 'CS'
		 GROUP BY t.team_id
                 ORDER BY points DESC, t.team_name) AS x

 

 

 

 

 

Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted (edited)

Or you could do it the easy way with a simple query and count the streak value in the code

<?php
include("db_inc.php"); // define host, username and password
include("myBar.php");  // graphic functions

$db = new mysqli(HOST,USERNAME,PASSWORD,'test'); // use your credentials
error_reporting(-1);


$sql = "SELECT team, result, weekno
        FROM fixture
        ORDER BY team, weekno";
$res = $db->query($sql);
$prevres='';
$table = "<table border='1' style='border-collapse:collapse'>\n
        <tr><th>Team</th><th>Week</th><th>Result</th><th>Streaks</th></tr>\n";
while (list($tm, $r, $wk) = $res->fetch_row()) {
    if ($r != $prevres) {
        $ws = $ls = $ds = 0; // clear streak counts on change of result
        $prevres = $r;
    }
    // increment the streak count
    switch ($r) {
        case 'win':  $ws++; break;
        case 'lose': $ls++; break;
        case 'draw': $ds++; break;
    }
    $table .= "<tr><td>$tm</td><td>$wk</td><td>$r</td>";
    
    // determine the bar colours and values
    if ($ws > 1) {
        $col = '#6F6';
        $strk = $ws;
    } elseif ($ds > 1) {
        $col = '#AAA';
        $strk = $ds;
    } elseif ($ls > 1) {
        $col = '#F33';
        $strk = $ls;
    } else {
        $col = '#FFF';
        $strk = 0;
    }
    // output the bar graphic
    $table .= "<td>" . bar($strk,5, 100, 20, $col) . "</td></tr>\n";
}        
$table .= "</table>\n";
?>
<!DOCTYPE html>
<html>
<head>
<title>Streaks</title>
</head>
<body>
<h3>Streaks</h3>
<?php
    echo $table;
?>
</body>
</html>

Giving

post-3105-0-46641400-1404832784_thumb.png

Edited by Barand
Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

wow now that is a good method of showing a streak...impressive...maybe something i'll do when im able to get the data I want from database :)

 

being a rookie at MySQL I had a good read about gathering more than one column from a sub query thanks to your help...although I have stumbled across one more while altering my query

 

basically im unable to get a result other than 0 when trying to get a win_percentage and win_ratio...any thoughts?...thank you again

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

FROM (SELECT l.league_id,
             t.team_id,
             t.team_name,
             plm.previous_rank,
             plm.rank_movement,
             plm.status,
             COUNT(fr.fixture_result_id) AS `matches_played`,
             SUM(IF(fr.result='Win', 1, 0)) AS `wins`,
             SUM(IF(fr.result='Loss', 1, 0)) AS `losses`,
             SUM(IF(fr.result='Draw', 1, 0)) AS `draws`,
             SUM(IF(fr.result='Win', l.win_points, IF(fr.result='Draw', l.draw_points, IF(fr.result='Loss', l.loss_points, 0)))) AS `points`,
             FORMAT(IFNULL(SUM(IF(fr.result='Win', 1, 0)) / COUNT(fr.fixture_result_id), 0), 2) AS `win_ratio`,
	     FORMAT(IFNULL(SUM(IF(fr.result='Win', 1, 0)) / COUNT(fr.fixture_result_id) * 100, 0), 2) AS `win_percentage`
                 
             FROM Leagues l
 
             LEFT JOIN player_league_mappings plm ON plm.league_id = l.league_id
             LEFT JOIN fixtures f ON f.challenger_player_id = plm.player_id OR f.opponent_player_id = plm.player_id
             LEFT JOIN fixture_results fr ON fr.player_id = plm.player_id
             LEFT JOIN player_team_mappings ptm ON ptm.player_id = plm.player_id
             LEFT JOIN teams t ON t.team_id = ptm.team_id
             LEFT JOIN team_game_mappings tgm ON tgm.team_id = t.team_id
             LEFT JOIN game_game_platform_mappings ggpm ON ggpm.game_game_platform_mapping_id = tgm.game_game_platform_mapping_id
             LEFT JOIN game_platforms gp ON gp.game_platform_id = ggpm.game_platform_id
             LEFT JOIN games g ON g.game_id = ggpm.game_id	     

             WHERE gp.game_platform_name = 'xbox 360' AND
                   g.game_abbreviation = 'CS:S' AND
                   l.league_name = 'Counter-Strike Team Death Match' AND
                   f.type = 'League'
		 GROUP BY t.team_id
                 ORDER BY points DESC, t.team_name) AS x
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

Without data I cannot see an obvious reason. But I would make a couple of general comments...

 

You have all your joins as LEFT JOINS. You only need A LEFT JOIN B when you want all from A even if there is no matching record in B. If there should be a match in B then use INNER JOIN. Left joins are really slow compared to inner joins.

 

Secondly, if you really do need a left join then the WHERE condition on the table that is jeft-joined should be in the join condition.

 

For example, use

...
LEFT JOIN fixtures f ON f.challenger_player_id = plm.player_id OR f.opponent_player_id = plm.player_id AND f.type = 'League'
... 
LEFT JOIN game_platforms gp ON gp.game_platform_id = ggpm.game_platform_id AND gp.game_platform_name = 'xbox 360'
LEFT JOIN games g ON g.game_id = ggpm.game_id AND g.game_abbreviation = 'CS:S'
WHERE
   l.league_name = 'Counter-Strike Team Death Match'
Link to comment
Share on other sites
Destramic Advanced Member

Destramic

Posted
  • Author
Posted

basically every table is joined in that query although I did change a few things about like you said (which I should of done before because it makes perfect sense)...now this query is returning everything I need without a sub query like the previous versions of this query...thank you once again for your help again

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


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

FROM (SELECT l.league_id,
             t.team_id,
             t.team_name,
             plm.previous_rank,
             plm.rank_movement,
             plm.status,
             COUNT(fr.fixture_result_id) AS 'matches_played',
             SUM(IF(fr.result='Win', 1, 0)) AS `wins`,
             SUM(IF(fr.result='Loss', 1, 0)) AS `losses`,
             SUM(IF(fr.result='Draw', 1, 0)) AS `draws`,
             SUM(IF(fr.result='Win', l.win_points, IF(fr.result='Draw', l.draw_points, IF(fr.result='Loss', l.loss_points, 0)))) AS `points`,
             FORMAT(IFNULL(SUM(IF(fr.result='Win', 1, 0)) / COUNT(fr.fixture_result_id), 0), 2) AS `win_ratio`,
	     FORMAT(IFNULL(SUM(IF(fr.result='Win', 1, 0)) / COUNT(fr.fixture_result_id) * 100, 0), 2) AS `win_percentage`,

@win_streak := IF(fr.result='Win', @win_streak +1, @win_streak = 0) AS `win_streak`,

@Loss_streak := IF(fr.result='Loss', @win_streak +1, @win_streak = 0) AS `loss_streak`,

@Draw_streak := IF(fr.result='Draw', @win_streak +1, @win_streak = 0) AS `draw_streak`
                 
             FROM Leagues l
 
             LEFT JOIN player_league_mappings plm ON plm.league_id = l.league_id
             INNER JOIN fixtures f ON f.challenger_player_id = plm.player_id OR f.opponent_player_id = plm.player_id AND f.type = 'League'
             INNER JOIN fixture_results fr ON fr.player_id = plm.player_id
             INNER JOIN player_team_mappings ptm ON ptm.player_id = plm.player_id
             INNER JOIN teams t ON t.team_id = ptm.team_id
             INNER JOIN team_game_mappings tgm ON tgm.team_id = t.team_id
             INNER JOIN game_game_platform_mappings ggpm ON ggpm.game_game_platform_mapping_id = tgm.game_game_platform_mapping_id
             INNER JOIN game_platforms gp ON gp.game_platform_id = ggpm.game_platform_id AND gp.game_platform_name = 'xbox 360'
             INNER JOIN games g ON g.game_id = ggpm.game_iD AND g.game_abbreviation = 'CS:S'     

             WHERE l.league_name = 'Counter-Strike Team Death Match'
             GROUP BY t.team_id
             ORDER BY points DESC, t.team_name) AS x
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.

×
 Share
Go to topic listing
×
×
  • 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.