MySQL - Optimize a Query and Find a rank based on column Sum

Question

I have a high score database for a game that tracks every play in a variety of worlds. What I want to do is find out some statistics on the plays, and then find where each world "ranks" according to each other world (sorted by number of times played).

So far I've got all my statistics working fine, however I've run into a problem finding the ranking of each world.

I'm also pretty sure doing this in three separate queries is probably a very slow way to go about this and could probably be improved.

I have a timestamp column (not used here) and the "world" column indexed in the DB schema. Here's a selection of my source:

function getStast($worldName) {
  // ## First find the number of wins and some other data:
  $query = "SELECT COUNT(*) AS total, 
            AVG(score) AS avgScore, 
            SUM(score) AS totalScore
            FROM highscores 
            WHERE world = '$worldName'
            AND victory = 1";
  $win = $row['total'];

  // ## Then find the number of losses:
  $query = "SELECT COUNT(*) AS total 
            FROM highscores 
            WHERE world = '$worldName'
            AND victory = 0";
  $loss = $row['total'];

  $total = $win + $loss;

  // ## Then find the rank (this is the broken bit):
  $query="SELECT world, count(*) AS total 
          FROM highscores 
          WHERE total > $total
          GROUP BY world
          ORDER BY total DESC";

  $rank = $row['total']+1;
  // ## ... Then output things.
}

I believe the specific line of code that's failing me is in the RANK query,

              WHERE total > $total

Is it not working because it can't accept a calculated total as an argument in the WHERE clause?

Finally, is there a more efficient way to calculate all of this in a single SQL query?

Answer 1

I think you might want to use 'having total > $total'?

SELECT world, count(*) AS total 
FROM highscores 
GROUP BY world
having total > $total
ORDER BY total DESC