title: Mysql count same values and group by max
body: <p>I write a report system so users can report any content by choosing some specific reasons.
  I have reports table and one of the columns named &quot;reason&quot; it is enum.</p>
  <p>I'm trying to list reported posts on moderation page, according to the column (reason) content with highest number in the table.</p>
  <p>The reports table:
  <a href="https://i.stack.imgur.com/b8yMM.png" rel="nofollow noreferrer">Reports</a></p>
  <p>&quot;violation&quot; is 2. Others are single. So on top of post there should be written &quot;violence&quot;.</p>
  <p>The posts table:
  <a href="https://i.stack.imgur.com/G7Vk3.png" rel="nofollow noreferrer">Posts</a></p>
  <p>What I try:</p>
  <pre><code>SELECT * FROM reports r
  INNER JOIN posts p
  ON r.contentid=p.id
  WHERE r.type=:type
  AND (SELECT reason, count(*)
  AS NUM FROM reports
  GROUP BY reason)
  GROUP BY p.id
  </code></pre>
  <p>I have no any data on the moderation page, help pls.</p>