result streak

[Destramic]

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]

Your subquery is returning more than one column.

 

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

[Destramic]

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

[Barand]

Rewrite your subquery to comply with the syntax rules

[Barand]

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]

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]

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]

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