title: Join + Count per row in postgresql
body: <p>Current "Attempts" table:</p>
  
  <pre><code>ID  QUESTION_ID   CORRECT
  1   1             FALSE
  2   2             TRUE
  3   4             FALSE
  4   3             FALSE
  5   1             TRUE
  6   1             TRUE
  7   4             TRUE
  8   3             TRUE
  9   4             FALSE
  10   1             TRUE
  11   2             TRUE
  11   1             FALSE
  11   3             FALSE
  </code></pre>
  
  <p>Current "Question" table:</p>
  
  <pre><code>ID  ANSWER
  1   A
  2   A
  3   B
  4   C
  5   C
  6   C
  7   C
  </code></pre>
  
  <p>Now I want to order Questions based on their amount of times solved. As you can see, Question 1 has been solved 3 times while Question 5, 6 and 7 have been solved 0 times.
  After I make this order, I want to pick a random top 5 of questions with lowest amount of solved.</p>
  
  <p>Therefore, my questions is: How to do this? My ultimate goal is to do this in rails, but first I want to experiment with postgresql a bit. So do you know how to do this? Code examples highly appreciated.</p>
  
  <p>Regards,</p>
  
  <p>Maurice</p>
  
  <p>// UPDATE</p>
  
  <p>Ok, I tried to put your advice into practise, but im running into a problem. As you can see, i'm just getting 1 row, i think because of the where clause. could it be that im counting it wrong?</p>
  
  <pre><code>=# select q.id, count(q.id)
  -# from questions as q
  -# left join attempts as a on a.question_id = q.id
  -# where a.correct = true and a.user_id = 4
  -# group by q.id
  -# order by count(q.id) desc
  -# limit 20
  -# \g
   id  | count 
  -----+-------
   112 |     2
  (1 row)
  </code></pre>
  
  <p>// UPDATE 2</p>
  
  <p>ok, i did a nested select to solve this problem (inspired by <a href="https://stackoverflow.com/questions/2613005/how-to-join-a-count-from-a-table-and-then-effect-that-count-with-another-join">How to JOIN a COUNT from a table, and then effect that COUNT with another JOIN</a>):</p>
  
  <pre><code>select q.id, temp.Count
  from questions as q
  left join
  (select q.id, count(a.id) as count
  from questions as q
  left join attempts as a
  on a.question_id = q.id
  where a.correct = true and a.user_id = 4
  group by q.id)
  temp on temp.id = q.id
  </code></pre>
  
  <p>Gave me something: an important list.</p>
  
  <p>Now, I still need to create the <strong>random 5 questions that have the least amount of counts</strong>.</p>
  
  <p>I'm trying to do something now with min(count), but that doesnt seem to work out. Any ideas on how to do this?</p>
  
  <p>Thanks again</p>