MySQL - working out the AVG for a subset of MAX values

Question

Hi all,

I have a table with columns 'Date, Name, Score'.

I wish to get the MAX(Score) for rows which share a common value (for e.g. the same date or even name), before averaging them to give me a figure, for example:

---- Date -----| -- Name -- | Score
2010-10-10 | John Smith | 86
2010-06-05 | Tedi Jones | 71
2010-10-10 | John Smith | 52
2010-06-05 | Tedi Jones | 68
2010-08-08 | Joe Bloggs | 79
2010-10-10 | John Smith | 46

So doing a MAX(Score) on the above would give me 86. However, what I'd like is the following:

MAX(Score) to give me the values 86 (MAX for date 10-10), 79 (MAX for date 08-08) and 71 (MAX for date 06-05) which I can then average to get 78.67. I'm hoping this is possible without having to resort to temp tables?

All replies are appreciated, thank you.

Answer 1

select Date, max(Score) as MaxScore
from MyTable
group by Date

If you want the Name as well, do

select m.Date, m.Name, m.Score
from (
    select Date, max(Score) as MaxScore
    from MyTable
    group by Date
) mm
inner join MyTable on mm.Date = m.Date 
    and mm.MaxScore = m.Score

Answer 2

Total average of daily maximal values:

SELECT AVG(dailyMax) AS avgOfDailyMax
FROM (SELECT Date, MAX(Score) AS dailyMax FROM MyTable GROUP BY Date) as DailyMaxTable 

and daily maximal values:

SELECT Date, MAX(Score) AS dailyMax
FROM MyTable
GROUP BY Date