CODEWITHSUNDEEP
sqljoincount
eluus
eluus

Reputation: 213

SQL multiple join with count

I have three tables :

FORM(id)

QUESTION(id, form_id, text)  --> X questions for a form

ANSWER(id, question_id, answer_number, text) --> X answers for a question

I also have a table for answers given by users :

USERS_ANSWERS(id, question_id, answer_number)

I would like to have, for each question, the COUNT given by answers, like :

question_id, answer_number, COUNT(users answer for this one)

If there is no answer, then count = 0.

Can you help me ? It takes me hours and I still have nothing. :(

PS : yes I know, why am I not using answer_id in USERS_ANSWERS ? I don't know, it isn't my database...

Upvotes: 0

Views: 58

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

A simple JOIN should do it;

SELECT q.id question_id, a.answer_number, COUNT(ua.id) answer_count
FROM question q
JOIN answer a 
  ON q.id = a.question_id
LEFT JOIN users_answers ua 
  ON q.id = ua.question_id AND a.answer_number = ua.answer_number
GROUP BY q.id, a.answer_number
ORDER BY q.id, a.answer_number

The first JOIN combines all questions with their answers to get all valid combinations. The following LEFT JOIN counts all answers for all combinations, allowing zero counts.

An SQLfiddle to test with.

Upvotes: 2

Bernd Linde
Bernd Linde

Reputation: 2152

You will need a sub query to get the count from your USERS_ANSWERS table for each question and answer record in the other two tables.

Below is an example of doing so, while also returning all questions even if they are unanswered:

select qst.id                       as 'question_id',
       isnull(ans.answer_number, 0) as 'answer_number',  -- isnull here because not all questions might have answers
       isnull((select count(1)
                 from USERS_ANSWERS usa
                where usa.question_id   = qst.id
                  and usa.answer_number = ans.answer_number), 0) as 'total user answers'
  from QUESTION qst
         left outer join -- left outer join here because not all questions might have answers
       ANSWER   ans on ans.question_id = qst.id

Upvotes: 0

David
David

Reputation: 3418

You simple need to group by (question_id, answer_number) and then count it

select 
question_id, answer_number, count(answer_number) as count
from user_answers 
group by question_id, answer_number
order by question_id, answer_number

Upvotes: 0

Related Questions