Sort MySQL query result by calculated average DESC

Question

I'm running a query on three columns; one column contains text, the other two contain numbers. I do a calculation on these numbers to get a new number called $average. I then spit out the result to an html table. The rows in the table are sorted in the order they come out of the database. I'm trying to sort the table so that the data is displayed from highest $average to lowest (while still be correctly associated with the correct text value from the first column).

I've tried some asort and foreach stuff, but I've only succeeded in making a mess of errors.

Any ideas as how I go about this? Thanks.

This is the current state of play:

/ db query
  if (!$result = mysqli_query($link,"SELECT quiz_name, 
                                            quiz_attempts, 
                                            cumulative_score 
                                       FROM scoredata")) {
  echo("There was a problem: " . mysqli_error($link));
  exit();
  }
...
// got results?
  if(mysqli_num_rows($result) >= 1) {
  
  $output = "";
  $output .= "<table>\n";
  $output .= "<tr><th>Quiz name</th> <th>Played</th> <th>Avg. score</th></tr>\n";
                           
  while($row = mysqli_fetch_array($result)) {

    $output .= "<tr><td>".str_replace('_', ' ', $row['quiz_name']) . "</td>";
    $output .= "<td>" . $row['quiz_attempts'] . "</td>";
    
    // calculate average score
    $average = $row['cumulative_score']/$row['quiz_attempts'];
    
    $output .= "<td>" . round($average,2) . "</td></tr>";
   }
                             
  $output .= "</table>\n";
  echo $output;
}
...

Answer 1

You can do calculation and sorting in your query:

SELECT 
    quiz_name, 
    quiz_attempts, 
    cumulative_score,
    (cumulative_score/quiz_attempts) as score_avg
FROM scoredata
ORDER BY score_avg DESC