query help

[Destramic]

hey guys im trying to make a league website and below the query calculates the wins losses and draws of a team in each row

 

what i want in this query is to make a points column which will add all the wins losses and draws up all together

 

win = 3 pts

draw = 2 pts

loss = 0 pts

 

so if a team has won a game and drawn a game the points colum will be 5...i hope this explains what i want...is this possible please?

 

 
$team_select  = "SELECT @rank := @rank + 1 AS 'rank',
                        t.team_id,
                        t.team_name, 
                        COUNT(r.league_match_result_id) AS 'matches_played',
                        COUNT(CASE r.result WHEN 'Win' THEN '3' END) AS 'wins', 
                        COUNT(CASE r.result WHEN 'Loss' THEN '0' END) AS 'losses',
                        COUNT(CASE r.result WHEN 'Draw' THEN '1' END) AS 'draws'
                 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
                 RIGHT JOIN league_matches m ON r.league_match_id = m.league_match_id
                 WHERE tl.league_id = :1
                 GROUP BY t.team_id
                 ORDER BY team_name";

[sasa]

try

$team_select  = "SELECT @rank := @rank + 1 AS 'rank',
                        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',2,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
                 RIGHT JOIN league_matches m ON r.league_match_id = m.league_match_id
                 WHERE tl.league_id = :1
                 GROUP BY t.team_id
                 ORDER BY team_name";

maybe is beter setup another table for points results an join it

table points

result points

Win      3

Lost      0

Draw    2

[Destramic]

hey sasa thanks for your reply...erm but what i need is to have a sum of all the wins and then times by 3....something like

 

SUM(wins * 3) + SUM(draws * 1) AS `points`

 

but i dont know how

[Destramic]

anyone know how i can calculate my wins and draw columns and save as points column please?

[sasa]

are you try this query

i'm just copy your query and i don't see tha you use ' instead ` change this

$team_select  = "SELECT @rank := @rank + 1 AS `rank`,
                        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',2,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
                 RIGHT JOIN league_matches m ON r.league_match_id = m.league_match_id
                 WHERE tl.league_id = 1
                 GROUP BY t.team_id
                 ORDER BY team_name";

this line 'SUM(IF(r.result='Win',3,IF(r.result='Draw',2,0))) AS `points`' means

if result is Win use 3 else if result is Draw use 2 else use 0 and sum this number

 

btw. DRAW != Draw

[Destramic]

thank you sasa that worked great thank you...now there is just one more problem i have...if a team has the same points then i want the rank to stop counting so the ranking order would be the same....example

 

rank    name        pts

  1      team 1      10

  1      team 2      10

 

instead of it looking like rank 1,2

 

	$team_select  = "SELECT @rank := @rank + 1 AS 'rank',
                        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
                 RIGHT JOIN league_matches m ON r.league_match_id = m.league_match_id
                 WHERE tl.league_id = :1
                 GROUP BY t.team_id
                 ORDER BY team_name";

 

is this possible?

 

 

[sasa]

$team_select  = "SELECT *, @rank := IF(@points=points, @rank, @rank+1), @points:=points 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
                 RIGHT JOIN league_matches m ON r.league_match_id = m.league_match_id
                 WHERE tl.league_id = :1
                 GROUP BY t.team_id
                 ORDER BY points, team_name) AS x";

[Destramic]

sasa its coming back with an error

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':1 GROUP BY t.team_id ORDER BY points,' at line 13

 

do you know why please?

 

but when i run the query in phpmyadmin it executes fine

[Destramic]

sorry i got it working now....its not returning the rank correctly as it should

 

(

    [0] => 2

    [team_id] => 2

    [1] => JAG

    [team_name] => JAG

    [2] => 2

    [matches_played] => 2

    [3] => 1

    [wins] => 1

    [4] => 1

    [losses] => 1

    [5] => 0

    [draws] => 0

    [6] => 3

    [points] => 3

    [7] => 1

    [@rank := IF(@points=points, @rank, @rank+1)] => 1

    [8] => 3

    [@points:=points] => 3

)

<tr>

<td> <img src="/images/arrow_down_red.gif" height="16" width="16" alt="hey"></td>

 

<td><img src="/images/countrys/united_kingdom.gif" height="15" width="21"> JAG</td>

<td>2</td>

<td>1</td>

<td>1</td>

<td>0</td>

<td>0</td>

 

<td>3</td>

</tr>

Array

(

    [0] => 1

    [team_id] => 1

    [1] => SAS

    [team_name] => SAS

    [2] => 1

    [matches_played] => 1

    [3] => 1

    [wins] => 1

    [4] => 0

    [losses] => 0

    [5] => 0

    [draws] => 0

    [6] => 3

    [points] => 3

    [7] => 2

    [@rank := IF(@points=points, @rank, @rank+1)] => 2

    [8] => 3

    [@points:=points] => 3

)

 

both teams should be ranked 1 becasue of being both on 3 points