title: How to count multiple values occurencies from table and pick max
body: <p>I'm struggling with making a proper mysql query. Here's the table:</p>
  
  <pre><code>**survey_result**
  question_id
  header_id
  answer
  </code></pre>
  
  <p>Where answer is int in (0,1,2,3,4,5) range. I'd like to make a query, where i will count how many times for specific id, a specific answer occurred. So for question_id = 1 i'd like to see:</p>
  
  <pre><code>question_id answer occurence_number
  1           0      12
  1           1      20
  1           2      34
  1           3      5
  1           4      9
  1           5      15
  </code></pre>
  
  <p>And most likely i'd like to pick an answer with either most or least occurrences. But that's the second part, i'm struggling with first one.</p>
  
  <p>With huge help with first statement i managed to finish it off, this is how working version looks:</p>
  
  <pre><code>select question_id, max(occurence_number) FROM
  (select question_id, answer, count(*) as occurence_number
  from survey_result
  group by question_id, answer
  order by question_id asc, occurence_number desc) as results
  GROUP BY question_id
  </code></pre>