MySQL query: select how many times was every option selected
I have 2 mysql tables
1. questions: with the following columns: id, title, answer1, answer2, answer3, answer4, answer5, nranswers.
and
2. answers with the following columns: id, questionid, userid, answer
Every question has maximum 5 answers( it can have between 2 and 5 answers). My problem is that I want to select from my database, for a given question, how many times was every option selected.
For exampl开发者_如何学JAVAe, let's suppose I have the question with the id 46, with 4 answers, and 48 users voted for the option #2, 37 users for the option #1 and 39 for the option #4.
I want a query that selects that and write these things:
1 37 2 48 3 0 4 39
P.S. VERY IMPORTANT! IT MUST COUNT ONLY NRANSWERS ANSWERS, AND IT MUST ECHO THE ONES THAT WEREN'T VOTED BEFORE.
Best way to do this: change table defs:
Questions (Question_ID, title)
Answers (Answer_ID, Question_ID, answer_text)
Votes (User_ID, Answer_ID)
Which contains the same data as your def, but is in first normal form. Selecting the counts is now really easy
SELECT
a.Answer_ID,
COUNT(v.User_ID)
FROM
Questions q
LEFT JOIN Answers a ON q.Question_ID = a.Question_ID
LEFT JOIN Votes v ON a.Answer_ID = v.Answer_ID
WHERE q.Question_ID = 46 -- or any other question ID
GROUP BY a.Answer_ID
ORDER BY a.Answer_ID;
SELECT q.id as question, a.answer as answer, count(a.answer) as count FROM questions q, answers a Group by q.id,a.answer
Problem with above that it will return as follows
question answer Count
1 1 37
1 2 48
1 4 39
1 3 0 this is missing
OR
SELECT a.question_id, a.answer, count(a.answer) FROM test.answers a Group by a.question_id, a.answer
精彩评论