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 http://stackoverflow.com/questions/2613005/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

How about somthing like the following?

SELECT Q.ID, COUNT()
FROM Questions AS Q
LEFT JOIN Attempts AS A ON A.QUESTION_ID = Q.ID
WHERE A.CORRECT = TRUE
GROUP BY Q.ID
ORDER BY COUNT() DESC
LIMIT 5

Summary: It joins the questions table to the attempts table, only considers rows where correct is true, groups by the question id, sorts by the count of true attempts per question, and then limits to the top five results.

I haven't tested it, but I think it should at least be close to what you are looking for.

Answer 2

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)