sum and max in the same query ?

MichaelHe

I have a little problem regarding a highscore list.

I have a table with the fields
PID - the profile id number
Category - Easy, Medium or Hard
Score - the score

Each profil has done the test more than ones in each category so each profile id has more than one score in each category.

I have made this query to get the highest score from each category added for a specific profile id.

$getscore=mysql_query("SELECT MAX(Score) AS Max FROM `QuizScore` WHERE `PID`='1' GROUP BY `Category`");
while ($row = mysql_fetch_array($getscore)){$max=$max+$row['Max'];}

This finds the highest score for profile 1 in each category and in the while statement I add them.

This works fine though I'm not sure if there's an easier way to add them.

But now I need to find the same for all the profiles and list them with the highest first.
Then I have to find the highest score for each profile in each category and add them in the query and order them by the sum. And this I just can't figure out.
For this to work I have to not only find the highest score for each profile in each category, but I also have to add the 3 highest in the query since I need to order by the final result.

PID, (highest in Easy+highest in Medium+highest in Hard) the sum
8, (7+5+13) 25
4, (3+11+9) 23
9, (5+7+6) 18
and so on

Roger_Ramjet

Highest score for each pid in each category

"SELECT pid, category, MAX(score) AS max FROM quizscore GROUP BY pid, category"

Add them together for each pid

"SELECT pid, SUM(max) AS sum FROM
(SELECT pid, category, MAX(score) AS max FROM quizscore GROUP BY pid, category) AS t
GROUP BY pid
ORDER BY sum DESC"

Can't think of a way to do it without the subquery.

MichaelHe

Thanks alot... I had no idea that I could put another query in like that...
And I couldn't see how this could be done in a single query, so this is just perfect, and I have justed tested it, and it works 🙂

Thanks alot

bradgrafelman

Don't forget to mark this thread resolved.