MySQL - get sum() grouped max() of group -
i have table structure below. each row 1 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 trying each person sum of best score in each month. s result of above should be:
person | sum(ofbestofeachmonth) --------------------------------- 32 | 932 65 | 1527 77 | 912
i know how fetch bests scores per userin month or range
select person, date, max(score) tabgames month(date) = 6 group person having (score>0)
because need in end output per quarter of year, fetching best each month , outside mysql adding.
now reading group-wise max , still try excpected results. help
subqueries:
select person, sum(best) (select person, max(score) best tabgames month(`date`) >= 1 , month(`date`) <= 6 group person, month(`date`)) bests group person
now subquery grouped person , month(date), return row each month.
Comments
Post a Comment