Have Geek Brain

MySQL: Can MAX (AVG (field)) be computed?

My current request reads:

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

cat_id

belongs to different categories: 1, 2, 3 or 4

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

Follow the link below, for example:

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

+2


source to share


3 answers


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
+3


source


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 will do the order of the result avg(rating)

and select the first row. and can make limit 1

, 1

to select the second maximum element

+2


source 


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
+1


source






More articles:


All Articles