sum limit question

michaelowen

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?

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

Here is an example:

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 sense?

Thanks!

-Michael

jmorris

This would change your database structure a little but it seemed to work to me.

name sex score test_num
bill rogers male 234 1
bill rogers male 23 2
bill rogers male 657 3
bill rogers male 34 4
.....
.....

Then use the select statement:


SELECT * FROM table WHERE sex='male' AND name='bill rogers'
ORDER BY score DESC limit 7

Hope this helps.

michaelowen

ahhh, i think i see what you mean.

Add another column that will rank the top 7 point values, and then collect those 7 for the sum?

please note there are 1000's of individuals in my database, and i am going to need to make this a dynamic looping script.

-mike

jmorris

Not exactly... what I meant was instead of using one_points, two_points, three_points, etc. put all of the scores into one column. That way you could pull from the database based on that one column. (ORDER BY score LIMIT 7). Previously you had ORDER BY total LIMIT 7 and it didn't seem like 'total' was a field in your database so it didn't know what to really order it by. I could be wrong though. I just added the test_num field to correspond with the number in one_points, two_points, etc. I didn't know if those numbers needed to be associated with the scores.

michaelowen

we can't put all those into multiple columss....i think you are missing what i'm saying.

take my example, and pretent there are 1000 people with 10 scores each......and each has to have it's own total.

here is a real work example:

I take 10 tests, and have 10 differnt scores.........i need to total the top 7 scores of those 10 tests. Imagine there are 1000 other people in my database who have all taken the same 10 tests, and have their own 10 scores, and just need to have their top 7 totalled.......and you'll see seperate columns wont' work.

-mike