sorting a table by calculated variable

[Jorn]

Hey guys,

 

Im trying to make a scoreboard for my pokersite so see who is the best player at the moment.

I calculate this by counting the total points for a player earned this season, and devide it by the rounds played.

This works well only I can't seem to figure out how to sort the table on the average score (points devided by rounds played).

since the sorting at current code is done on a SQL column, I was wondering if it is possible to change that.

The code automaticly adds nummers to the rows to see who i 1 through 6.

 

here is the relevant code part:

{   
// SELECT * FROM ".$score_table."  WHERE tournamentid = '" . $cgi->htmlEncode ( $row [ "tournamentid" ] ) . "' ORDER BY points DESC
 $rows = $sql->execute ( "SELECT playerid,SUM(points) AS points,count(playerid) AS ronden FROM ".$score_table."   GROUP BY playerid ORDER BY points DESC", SQL_RETURN_ASSOC ) or die ("$DatabaseError"); 
  echo "<table border=\"1\" align=\"center\" cellpadding=\"4\" cellspacing=\"1\" bgcolor=\"#000000\" bordercolor=\"#e17400\">
<tr bgcolor=\"#000000\" bordercolor=\"#e17400\"><td>Positie</td><td>Poker King</td><td>Punten totaal</td><td>Gespeelde ronden</td><td>Gemiddelde</td></tr>";
    $num = sizeof ( $rows );      
     for ( $i = 0; $i < $num; ++$i )
      {
	$ronden = $rows [ $i ] [ "ronden" ];
	$points = $rows [ $i ] [ "points" ];
	$playerid = $rows [ $i ] [ "playerid" ];
	$gem = round($points/$ronden,2);
	$pos = $i + 1;
echo "<tr bgcolor=\"#000000\">
<td align=\"center\" >$pos</td>
<td>";
$sql->execute ( "SELECT playerid FROM ".$player_table." AS playerid",
    SQL_RETURN_ASSOC );
    $prow = $prows [ 0 ]; 
  if ($prow['profile']) { echo "<a href=\"?op=showplayer&pid=$playerid\" title=\"View Player Information\" >$prow[name]</a>";  }
  else { echo "$prow[name]";  }
  
echo"$playerid</td>
<td align=\"center\" >$points</td>
<td align=\"center\" >$ronden</td>
<td align=\"center\" >$gem</td>
</tr>";

}
echo "</table>";
  }

}

 

here is the result:

 

 

As you can see player 6 should be at position 5.

 

p.s. sorry for the dutch column names 

translation: Positie = Position , punten totaal = total points , Gespeelde ronden = rounds played , gemiddelde = average

[requinix]

Add an AVG() and the rest should be obvious.

SELECT playerid, SUM(points) AS points, COUNT(playerid) AS ronden, AVG(points) AS avgpoints FROM $score_table GROUP BY playerid ORDER BY avgpoints DESC

[Jorn]

Worked like a charm! thanks