MySQL - get sum() grouped max() of group

Question

I have table structure like below. Each row is one played game, each person can play many or none times in each month.

id  |  person  |  score  |   date  |
------------------------------------
1   |    32    |  444    | 2011-05 |
2   |    65    |  528    | 2011-05 |
3   |    77    |  455    | 2011-05 |
4   |    32    |  266    | 2011-06 |
5   |    77    |  100    | 2011-06 |
6   |    77    |  457    | 2011-06 |
7   |    77    |  457    | 2011-06 |
8   |    65    |  999    | 2011-07 |
9   |    32    |  222    | 2011-07 |

I am trying to get for each person sum of its best score in each month. S result of above should be:

 person  | SUM(ofbestofeachmonth)
---------------------------------
  32     |  932
  65     |  1527
  77     |  912

I know how to fetch the bests scores per userin month or some range

SELECT person, date, MAX(score) FROM tabgames WHERE MONTH(date) = 6 GROUP BY person HAVING (score>0)

Because i need in the end output per quarter of year, now i am fetching best for each month and outside the MySQL i am adding.

Now i am reading about group-wise max and still try to get excpected results. Any help

jakubka · Accepted Answer · 2011-07-03T16:14:32.447

9

Subqueries:

SELECT person, SUM(best)
FROM
    (SELECT person, MAX(score) as best
    FROM tabgames
    WHERE MONTH(`date`) >= 1 AND MONTH(`date`) <= 6 
    GROUP BY person, MONTH(`date`)) as bests
GROUP BY person

Now the subquery is grouped by person and MONTH(date), so it will return a row for each month.

edited Jul 03 '11 at 16:14

answered Jul 01 '11 at 21:52

jakubka

706
1
9
23

i thought like this but this subquery returns max score for each person for whole range not max for each month. There should be some Join, trying but still stuck – carpereret Jul 01 '11 at 22:15
thanks, that's it. Notice: not im my case but this might return wrong rows if turn of the year is the range - just add YEAR to where. – carpereret Jul 04 '11 at 09:54

score 0 · Answer 2 · answered Jul 03 '11 at 16:28

0

Select t.person,Max(t.Score) from 
(
Select person,Sum(Score) as Score,Month('Date') as [Month]  From tabgames
WHERE MONTH(`date`) >= 1 AND MONTH(`date`) <= 6 
GROUP BY PERSON,Month

) AS t

answered Jul 03 '11 at 16:28

Ovais Khatri

3,201
16
14

2

For readability : `WHERE MONTH(date) >= 1 AND MONTH(date) <= 6` can be rewritten : `WHERE MONTH(date) BETWEEN 1 AND 6` – Arnaud F. Jul 03 '11 at 16:36

MySQL - get sum() grouped max() of group

2 Answers2

Linked