CODEWITHSUNDEEP
phpmysql
Oscar
Oscar

Reputation: 1103

How to get SUM of only highest score in mysql and sort by category?

What I want to do is get the SUM of only highest score in my database and sort by category

here is my database table

question_id  score1 score2 score3 category_id 
-----------  ------ ------ ------ -----------
   1           4      3       1        1
   2           5      2       9        2
   3           7      2       1        1
   4           1      5       6        2

What I want in my result

it is only get the highest score to add
category_id 1 = score 11 (7+4)
category_id 2 = score 15 (9+6)

Any idea how to solve it using php or mysql?

thanks

Upvotes: 1

Views: 476

Answers (4)

Ja͢ck
Ja͢ck

Reputation: 173642

You would need the GREATEST function for that; it returns the biggest of all sums for the respective group.

SELECT category_id, GREATEST(SUM(score1), SUM(score2), SUM(score3))
FROM mytable
GROUP BY category_id;

Btw, this is different than calculating the sum of each greatest value per row. For that, you need to reverse GREATEST and SUM.

Upvotes: 3

Alex Siri
Alex Siri

Reputation: 2864

I think the right query to do that would be:

SELECT category_id, greatest(sum(score_1), sum(score_2), sum(score_3))
  from scores
  group by category_id;

That would give you the greatest of the scores per column.

But, you can do 

SELECT category_id, sum(greatest(score_1,score_2,score_3))
  from scores
  group by category_id;

If you want the greatest for each row, and then sum them.

You can check the fiddle in http://sqlfiddle.com/#!2/2d7a2/5

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

You can use GREATEST to find the max of the columns, then SUM over that;

You're not quite making it clear if you want the sum of the highest scores per question, or the highest score overall, so I'll add both :)

This will give you the sum of the highest scores per question, that is, if score1 is highest on question1 and score2 is highest on question2, they'd be summed;

SELECT category_id,SUM(GREATEST(score1,score2,score3))
FROM questions
GROUP BY category_id

An SQLfiddle to test with.

This will instead give you the highest total score of score1, score2 and score3, that is if score1's total is higher than score2's total, it will return the sum of score1;

SELECT category_id, GREATEST(SUM(score1),SUM(score2),SUM(score3))
FROM questions
GROUP BY category_id

Another SQLfiddle.

Upvotes: 2

Sundar
Sundar

Reputation: 4650

This may helps,

SELECT SUM(score1+score2+score3) AS score, 
             category_id FROM table GROUP BY question_id ORDER BY score desc

Upvotes: 0

Related Questions