MySQL: where count is higher than average -

i want select posts users have specific followers higher overall average (compared other users)

the problem when use avg() limits number of posts/users coming through, yet can't use group j.id break average count , where j2.fcount >= j2.oavg stops working properly

here's code

select * (       select j.*, round(avg(j.fcount)) oavg    (      select  p.id , count(fcount.id)  fcount  `post` p  left join `table` table on  ... left join `user` user on .... left join `follow` fcount on fcount.user_id=user.id , fcount.follow_id=table.ids p.user_id=fcount.user_id group p.id   ) j   ---- >   `group j.id` - breaks average below   ) j2   j2.fcount >= j2.oavg  

thank :)

because you're trying compare average, might have inner query twice this.

select *,   (select avg(fcount) average       (select count(fcount.id) fcount        post p        left join follow fcount on fcount.user_id = p.user_id        group p.id        )j1    )as average   (select p2.id, count(fcount2.id) fcount     post p2     left join follow fcount2 on fcount2.user_id = p2.user_id     group p2.id  )j2 having fcount >= average 

sqlfiddle replace inner queries of j1 , j2 j

if want run inner query once can use user-defined variables total count divide count calculate own average this

select id,fcount,@sum/@count average (select id,        fcount,        @sum := @sum + fcount total,        @count := @count + 1 posts        (select p.id,count(fcount.id) fcount        post p        left join follow fcount on fcount.user_id = p.user_id        group p.id        )j,        (select @sum:=0.0,@count:=0.0)initialize )t having fcount >= average 

sqlfiddle