Join + Count per row in postgresql

Question

Current "Attempts" table:

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

Current "Question" table:

ID  ANSWER
1   A
2   A
3   B
4   C
5   C
6   C
7   C

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.

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.

Regards,

Maurice

// UPDATE

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?

=# 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)

// UPDATE 2

ok, i did a nested select to solve this problem (inspired by How to JOIN a COUNT from a table, and then effect that COUNT with another JOIN):

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

Gave me something: an important list.

Now, I still need to create the random 5 questions that have the least amount of counts.

I'm trying to do something now with min(count), but that doesnt seem to work out. Any ideas on how to do this?

Thanks again

Answer 1

SELECT Q.ID, COUNT(A.ID) AS SolveCount
FROM Question Q
LEFT JOIN Attempts A
ON A.QUESTION_ID = Q.ID
WHERE A.CORRECT = TRUE
GROUP BY Q.ID
ORDER BY COUNT(A.ID)