[Resolved] MySQL - SUM of top 6 values in column

PinkFloyd007

Hello people,

I've got two tables. One with people details, and one with scores.

The table with people details has a unique identifier 'PID' column (numeric) and the person's full name 'PNAME'.

The scores table has a unique identifier 'RID' column (numeric), the person identifier 'PID' and a score 'RSCORE'.

Every person has between 1 and 8 scores.

What I now need to do, is output a table with the person's name, the total (SUM) of the 6 highest scores for that person, and those 6 individual scores, from highest to lowest. The table needs to be ordered descending on the total.

Like this :

people table :

PID PNAME

001 Jelle
002 Ludo
003 Sadri

scores table :

RID PID RSCORE

001 001 275
002 002 277
003 003 282
004 001 271
005 002 278
006 003 280
007 001 271
008 002 275
009 003 281
010 001 269
011 002 273
012 003 281
013 001 271
014 002 278
015 003 279
016 001 275
017 002 269
018 003 291
019 001 272
020 002 276
021 003 278
022 001 274
023 002 270
024 003 285

Resulting output table :

PNAME TOTAL SC1 SC2 SC3 SC4 SC5 SC6

Sadri 1700 291 285 282 281 281 280
Ludo 1675 278 278 277 276 275 273
Jelle 1638 275 275 274 272 271 271

Most of this, I can already do, but I get stuck on the SUM of only the top 6 scores... All I can manage is a full total of all scores for that person.

Who can help me on this one ?

Thanks already!

bastien

2 ways I can see to do this.

Use a temporary table to first create the six top records for each user and then sum that
get the all the data and use loops and an array to get the data into the format that you need

PinkFloyd007

And how would one do this ?

[I should write "newbie" on my forehead...]

tomhath

What database are you using? If MySQL, what version? This can be done with subqueries, but MySQL didn't support them before 4.1

PinkFloyd007

MySQL 3.23.58 but moving soon to a new server running MySQL 4.0.23.

tomhath

Without subqueries I'd use bastien's #2. This code is not very pretty, but it will work:

<?php

$names = array();
$totals = array();
$allscores = array();

#################################
# echo each line of the table.
# The final </tr> is produced as
# HTML so the ouput looks better
# the viewed as source.
#################################
function echoline($n, $t, $s) {
  echo "<tr><td>" . $n . "</td><td>" . $t  . "</td>" .
           "<td>" . $s[0] . "</td>" . 
           "<td>" . $s[1] . "</td>" . 
           "<td>" . $s[2] . "</td>" . 
           "<td>" . $s[3] . "</td>" . 
           "<td>" . $s[4] . "</td>" . 
           "<td>" . $s[5] . "</td>";
?>
</tr>
<?php
}

#################################
# save each name/total/6 highest scores
#################################
function oneline ($n, $t, $s) {
  global $names, $totals, $allscores;
  array_push($names,$n);
  array_push($totals,$t);
  array_push($allscores,$s);
}

include "connectvars.php";
$conn = mysql_connect($server, $username, $password)
   or die ("Connect error: " . mysql_error());

mysql_select_db('test', $conn)
   or die ("Select db error: " . mysql_error());

$query = "select p.pname, s.rscore " .
         "from people p " .
         "inner join scores s " .
         "where p.pid = s.pid " .
         "order by p.pname, s.rscore desc";

$res = mysql_query($query)
   or die ("Query error: " . mysql_error());

$name = "";
while ($row = mysql_fetch_assoc($res)) {
  if ($name != $row['pname']) {
    if ($name != "") {
      oneline($name, $total, $scores);
    }
    $scores = array(0=>'&nbsp;',1=>'&nbsp;',2=>'&nbsp;',
                    3=>'&nbsp;',4=>'&nbsp;',5=>'&nbsp;');
    $total = 0;
    $name = $row['pname'];
    $i = 0; 
  }
  if ($i <= 5) {
    $scores[$i] = $row['rscore'];
    $total = $total + $row['rscore'];
  }
  $i++;
}
oneline($name, $total, $scores);

# sort all arrays by total, highest first
array_multisort($totals, SORT_DESC, $names, $allscores);

?>
<table border='1'>
<th>Name</th><th>Total</th><th colspan='6'>Scores</th>
<?php

# produce the HTML
for ($i=0; $i<count($names); $i++) {
  echoline($names[$i], $totals[$i], $allscores[$i]);
}
echo "</table>";
?>

PinkFloyd007

Marvelous!!!

That worked out super!

One last question though : would it be possible to add a first column, giving the resulting ranking number for each row, first row being 1 and counting up ?

Thanks a million!

tomhath

The last loop where it's calling the echoline function already has the number $i, just add that to the parameters passed to echoline and echo it out.

PinkFloyd007

I edited what you told me to do a bit further, so that the first person would not have the rank 0.

Works like hell!