mysql分组后sum,MySQL - 获取sum（）分组的max（）

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

解决方案

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.