mysql select greatest value from multiple sum

Question

I am using the following results table

Results Table
__________________________________________
player_a| player_b | player_c |   year 
________|________  |__________|___________|
100      150        150        2015  
100      -50        -50        2015 
100      350        250        2014
200      350        250        2014

What i would like to do is get the sum for each player per year. Using the following Select i get the desired results.

SELECT (

SELECT SUM( player_a )
FROM `results`
WHERE year =2015
) AS player_a_2015, (

SELECT SUM( player_a)
FROM `results`
WHERE year =2014
) AS player_a_2014

How can i get the maximum sum result only ?

ie For player_a should be 300

M0rtiis · Accepted Answer · 2015-07-26 07:42:56Z

1

SELECT SUM(player_a) AS spa
FROM results
GROUP BY year
ORDER BY spa DESC
LIMIT 1

answered Jul 26, 2015 at 7:42

M0rtiis

3,7611 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexandros Tseros Over a year ago

thank you very much, that's exactly what i was looking for!

Abhishek Bhardwaj · Accepted Answer · 2015-07-26 07:49:40Z

0

You may wanna use,

SELECT MAX(SUM_A),YEAR FROM (
    SELECT SUM( player_a ) as SUM_A, YEAR
    FROM results
GROUP BY YEAR
) A_SCORE;

This will do it in one go(obviously for one player).

answered Jul 26, 2015 at 7:49

Abhishek Bhardwaj

2421 gold badge2 silver badges8 bronze badges

Comments

ash · Accepted Answer · 2015-07-26 07:49:59Z

0

This should do it.

Make sure you wrap your OR condition in parenthesis so it evaluates them both.

SELECT SUM(player_a) AS sum_score
  FROM results
 WHERE (year = '2015' OR year = '2014')

EDIT

If you're wanting the maximum score by year, and then to sum that - this should be OK.

SELECT MAX(max_sum_score) AS max_sum_score
  FROM ( SELECT year
              , SUM(player_a) AS max_sum_score
           FROM results
          WHERE (year = '2015' OR year = '2014')
          GROUP BY year 
       ) a

edited Jul 26, 2015 at 7:49

answered Jul 26, 2015 at 7:40

ash

1,2324 gold badges26 silver badges47 bronze badges

4 Comments

ash Over a year ago

Actually, looking at it - how would it come to 300? The answer I posted would get you 500 - are you wanting the Maximum score for each year?

Alexandros Tseros Over a year ago

maybe i didnt explain it correctly. i would like to get the highest value, from the 2 sums, not the sum of those 2

ash Over a year ago

Just setting up a table in MySQL to test that SQL for you... 1 sec

ash Over a year ago

Yep - the edited SQL returns 300 in the table I have setup to test.

Collectives™ on Stack Overflow

mysql select greatest value from multiple sum

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related