title: MySQL: where count is higher than average
body: <p>I want to select posts from users who have specific followers which is higher than the overall average (compared to other users)</p>
  
  <p>The problem is when I use <code>AVG()</code> it limits the number of posts/users coming through, yet I can't use <code>GROUP BY j.id</code> as it will break the average count and  <code>WHERE j2.fCount &gt;= j2.oAvg</code> stops working properly</p>
  
  <p>Here's my code</p>
  
  <pre><code>SELECT * FROM ( 
  
      SELECT j.*, ROUND(AVG(j.fCount)) as oAvg
     FROM ( 
  
     SELECT  p.id , COUNT(fCount.id)  as fCount
   FROM `post` p
  
  LEFT JOIN `table` table ON  ...
  LEFT JOIN `user` user ON ....
  LEFT JOIN `follow` fCount ON fCount.user_id=user.id AND fCount.follow_id=table.ids
  WHERE p.user_id=fCount.user_id
  group by p.id
  
  
  ) j 
   ---- &gt;   `GROUP BY j.id` - BREAKS THE AVERAGE BELOW 
   ) j2
  
  
  WHERE j2.fCount &gt;= j2.oAvg 
  </code></pre>
  
  <p>Thank you :) </p>