Counting help please :)

[jmcall10]

Hi, I have the following:

 

$rows = $sql->execute ( "SELECT playerid,SUM(points) AS points FROM ".$score_table."  GROUP BY playerid ORDER BY points DESC", SQL_RETURN_ASSOC ) or die ("$DatabaseError");   
echo "<table><tr><td>Pos.</td><td>Player</td><td>Played</td><td>Won</td><td>Points</td></tr>";
    $num = sizeof ( $rows );      
      for ( $i = 0; $i < $num; ++$i )
      {
	$points = $rows [ $i ] [ "points" ];
	$playerid = $rows [ $i ] [ "playerid" ];
	$pos = $i + 1;
echo "<tr>
<td>$pos</td>
<td>$playerid"</td>	  
<td align=\"center\"></td>
<td align=\"center\"></td>
<td align=\"center\">$points</td>
</tr>";

 

 

Now my points table looks like this:

 

id, playerid, tournamentid, points, dateadded

 

As I hope you can see I ahve added two column, one us "Played", and the other is "Won"

 

At the moment the above query sorts out who has the most points and orders them accordingly.

 

What I want to add in is the details of these two columns.

 

Could someone please assist

 

Thanks in advance

 

jmcall10

 

[jmcall10]

I have managed to sort out the how many times people have played column.

 

Can someone please assist with a query that will be able to show how many times they have won.

 

To break the tables down for you.

 

Basically there is the score table which has the following fields:

id, playerid, tournamentid, points, dateadded,

 

So a player can play in many tournaments and will get points. They may win a tournament therefore would have the most points allocated to them under that tournament.

 

So what I guess I need the query to do is work out how many times someone has been allocated the most points.

 

thanks in advance

 

jmcall10

[fenway]

Well, if you use group by tournamentID with MAX(), you'll get the player who won for each... then if you wrap this again, you should be able to COUNT() with GROUP BY userID.