Page 1 of 1
sum limit problem
Posted: Tue Nov 09, 2004 12:02 pm
by speedamp
hello everybody.....here is my dilemna:
each entrant to a database has 10+ scores, and i need to add the 7 highest (as total).
Can I do this? I'm close i think, but i can't get this valid. Should I use SUM, COUNT, CONCAT, etc to return ONLY the top 7 values?
Code: Select all
SELECT one_points, two_points, three_points, four_points, five_points, six_points, seven_points, eight_points
FROM table
WHERE sex = 'male'
ORDER BY total ASC
LIMIT 7
Thanks!
-Michael
Weirdan | Help us, help you. Please use Code: Select all
tags where approriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Posted: Tue Nov 09, 2004 12:08 pm
by Weirdan
perhaps something like this:
Code: Select all
SELECT name, one_points+two_points+three_points+four_points+five_points+six_points+seven_points+eight_points as total
FROM table
WHERE sex = 'male'
ORDER BY total DESC
LIMIT 7
This query selects names of those males who has highest sum of eight columns.
Posted: Tue Nov 09, 2004 12:13 pm
by speedamp
no, that won't do it..... i don't want the ranking among the entrants.
Each entrants total needs to be the highest 7 of his scores.
For example, each person has 10 point values.....i need to total that top 7, and assign that number as "total"
-mike
Posted: Tue Nov 09, 2004 12:23 pm
by Weirdan
do you need to get the highest scores ever? Like:
Code: Select all
Max scores ever:
one_points: 123
two_points: 345
three_points: 234
etc....
?
Posted: Tue Nov 09, 2004 12:38 pm
by swdev
Not following you here,
Post the table definition, some sample data and the results you want to see.
Posted: Tue Nov 09, 2004 12:50 pm
by speedamp
actually the concept isn't that tough, but I think i'm explaining it strangely.
Here is an example:
Code: Select all
field_name value
---------------- ---------------
name: Bill Rodgers
one_points 100
two_points 200
three_points 29
four_point 0
five_points 700
six_points 1101
seven_points 26
eight_points 100
nine_points 4
ten_points 224
What I need to do is calculate the TOTAL of the 7 highest values from this structure.
Does that make more sense?
-mike
Posted: Tue Nov 09, 2004 1:42 pm
by Weirdan
speedamp wrote:
Does that make more sense?
That does, but most RDBMS aren't designed for such tasks

Hence you need to do that in your client application, something like this:
Code: Select all
//... connect to the db
$rs = mysql_query('select * from table');
$res = array();
while($q = mysql_fetch_row($rs)) $res[] = $q;
foreach($res as $row)
$name = array_shift($row);
rsort($row); // sort in reverse order
$total = array_sum(array_slice($row, 0, 7)); // sum up 7 highest scores
echo $name . ' has achieved the total score of ' . $total . ' points';
}
Posted: Mon Nov 29, 2004 2:39 pm
by speedamp
I rewrote this a bit, and wanted to know how to limit the 7 highest POINT totals into a seperate value?
Code: Select all
<?php
$result = mysql_query('SELECT * from table');
while ($row = mysql_fetch_assoc($result))
{
foreach ($row as $key => $value)
{
if (strstr($key, '_points')) {$points[] = $value;}
}
$sum_array[$row['table_id']] = array_sum($points);
$data_array[$row['table_id']] = $row;
}
arsort($sum_array);
echo 'the total combined scores are:<br><br>';
foreach ($sum_array as $key => $value)
{
echo '
ID: ' . $data_array[$key]['table_id'] . '<br>
first name: ' . $data_array[$key]['first_name'] . '<br>
last name: ' . $data_array[$key]['last_name'] . '<br>
total score: ' . $value . '<br><br>
';
}
?>
where do i put the limit?
thanks,
-mike