sum limit problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
speedamp
Forum Commoner
Posts: 45
Joined: Tue Apr 29, 2003 3:59 pm

sum limit problem

Post 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

and

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]
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
speedamp
Forum Commoner
Posts: 45
Joined: Tue Apr 29, 2003 3:59 pm

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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....
?
swdev
Forum Commoner
Posts: 59
Joined: Mon Oct 25, 2004 8:04 am

Post by swdev »

Not following you here,

Post the table definition, some sample data and the results you want to see.
speedamp
Forum Commoner
Posts: 45
Joined: Tue Apr 29, 2003 3:59 pm

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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';
}
speedamp
Forum Commoner
Posts: 45
Joined: Tue Apr 29, 2003 3:59 pm

Post 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
Post Reply