[Resolved] Rank position in MySql

MannyLS

Hi,

I'm a beginner in PHP/MySql an I have this question:

Lets say I create a table "trimester" with these fields:

Employee, T1, T2, T3, T4

For each Trimester, I enter the total sales of each employee and then, I will group the results by "Total Sales" as follows:

$sql = 'SELECT Employee, T1, T2, T3, T4, SUM( T1 + T2 + T3 + T4 ) AS "Total Sales" FROM `trimester` GROUP BY Employees ORDER BY `Total Sales` ASC LIMIT 0 , 30", $db);

Now I would like to create a column that will show the employee's rank according to his or her total sales and that would be automatically generated, example:

Rank - Employee - T1 - T2 - T3 - T4 - Total Sales
1 John 20 20 20 10 70
1 Joey 30 20 10 10 70
3 Paul 30 10 10 10 60

Since John and Joey have the same total, their rank will be 1 and Paul would be 3rd and not 2nd since John and Joey would share the commission given to 1st and 2nd. In anycase, the "rank" doesn't exist because I don't know how to make it. How do I create the column "rank" (In MySql or in my PHP code?)

Thanks in advance for your help

bastien

simpler with php...create a counter and an if then statement


$rank=0;                     //initialize counter
$old_total=0;              //set a comparison value initialized to 0

echo "<table>";

while ($rows=mysql_fetch_array($result)){

  [color=red]//set variables - not required but provided for clarity[/color]
  $name=$rows['name'];
  $T1=$rows['T1'];
  $T2=$rows['T2'];
  $T3=$rows['T3'];
  $T4=$rows['T4'];
  $Ttotal=$rows['Total Sales'];


  [color=red]//if the totals values are the same then show the results 
  //else add one to the rank and change the old total to the 
  //current value[/color]
  if ($old_total!=$Ttotal){

      [color=red]//increase the rank counter[/color]
      $rank++;

      [color=red]//change the old total value to match the new total[/color]
      $old_total=$Ttotal;

   }//end if



   [color=red]//show the details[/color]
   echo "<tr><td>$rank</td><td>$name</td> <td>$T1</td>
             <td>$T2</td><td>$T3</td><td>$T4</td>
             <td>$Ttotal</td></tr>";


}//end while

echo "</table>";

tomhath

Your SQL looks wrong to me.
Will there be more than one row per Employee?
If so you won't be getting the correct values for T1,...,T4.
If not (i.e. there's only one row per Employee), you don't need the SUM() and Group By;
although what you have will give the correct answer.

SELECT Employee, T1, T2, T3, T4, 
T1 + T2 + T3 + T4  AS "Total Sales" 
FROM `trimester`  

ORDER BY `Total Sales` ASC LIMIT 0 , 30

And you might want to ORDER in DESC instead of ASC, unless you want the Employee with the lowest sales to come in first...

MannyLS

You are both right and thanks for sharing your knowledge with me. Every day I find new things about PHP that amaze me.
Now, Bastien's formula to determine rank did in fact works fine and in my test I have these results :

1 Brian 10 1 50 44 105
2 Manny 30 20 10 10 70
2 John 30 20 10 10 70
3 Joey 10 20 22 12 64

How can I adjust the counter $rank so Joey's position would be 4 instead of 3?

Thanks

jayant

Add
$rank_temp=0;
below
$rank=0;

change the if statement you see below:

      if ($old_total!=$Ttotal){

      //increase the rank counter
      $rank++;

      //change the old total value to match the new total
      $old_total=$Ttotal;

   }//end if

      $rank_temp++;
  if ($old_total!=$Ttotal){
      $rank = $rank_temp;
      $old_total=$Ttotal;
   }

MannyLS

Oh yes! Now I have to study all this, thanks to all :-)