CODEWITHSUNDEEP
mysqlmaxaverage
cbros2008
cbros2008

Reputation: 353

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

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.

Upvotes: 1

Views: 1462

Answers (2)

eumiro
eumiro

Reputation: 212835

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

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

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

Upvotes: 1

Related Questions