CODEWITHSUNDEEP
mysqlaverage
Brad
Brad

Reputation: 57

MySQL: Is it possible to compute MAX( AVG (field) )?

My current query reads:

SELECT entry_id, user_id, cat_id, AVG( rating ) as avg_rate
FROM `entry_rate`
WHERE 1
GROUP BY entry_id

cat_id relates to different categories: 1, 2, 3 or 4

Is there a way I can find the maximum average for each user in each category without setting up an additional table? The return could potentially be 4 maximum avg_rate for each user_id

Visit the link below for example:

http://lh5.ggpht.com/_rvDQuhTddnc/S8Os_77qR9I/AAAAAAAAA2M/IPmzNeYjfCA/s800/table1.jpg

Upvotes: 4

Views: 10840

Answers (4)

narasimha
narasimha

Reputation: 11

SELECT entry_id, user_id, cat_id, AVG( rating ) as avg_rate FROM entry_rate GROUP BY entry_id order by avg_rate desc limit 1;

Upvotes: 0

Sami Ghname
Sami Ghname

Reputation: 61

You can make this statement.

SELECT entry_id, user_id, cat_id, AVG( rating ) as avg_rate

FROM 'entry_rate'

GROUP BY entry_id

order by AVG( rating ) DESC

limit 1

this make the result order by avg(rating) and select the first row. and can make the limit 1,1 to select the second max element

Upvotes: 4

tloflin
tloflin

Reputation: 4050

May not be the most efficient way:

select user_id, cat_id, MAX(avg_rate)
FROM (
    SELECT entry_id, user_id, cat_id, AVG( rating ) as avg_rate
    FROM entry_rate
    GROUP BY entry_id, user_id, cat_id) t
GROUP BY user_id, cat_id

Upvotes: 5

nos
nos

Reputation: 229342

SELECT s.user_id,s.cat_id,max(s.avg_rate) FROM (
  SELECT entry_id, user_id, cat_id, AVG( rating ) as avg_rate
  FROM entry_rate
  GROUP BY entry_id,user_id,cat_id) as s 
GROUP BY  s.user_id,s.cat_id

Upvotes: 2

Related Questions