result streak

Destramic · July 6, 2014

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

Barand · July 6, 2014

Your subquery is returning more than one column.

http://lmgtfy.com/?q=ERROR+1241+-+OPERAND+SHOULD+CONTAIN+1+COLUMN

Destramic · July 6, 2014

thank you...what would be the best way to get the streak results im after please?

Barand · July 7, 2014

Rewrite your subquery to comply with the syntax rules

Barand · July 8, 2014

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

Edited July 8, 2014 by Barand

Destramic · July 9, 2014

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

Barand · July 9, 2014

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'

Destramic · July 9, 2014

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

Sign In

result streak

Recommended Posts

Destramic

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Destramic

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Destramic

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Destramic

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information