How would I perform math within a sql query to calculate percent difference?
I would like to take surveys (2 or more choices), and place the results in a table. I just want to know how I would tally up the results in a sql query.
Even more, should i be doing the math within the query, or just use PHP to do the math?
Example
Questions Table
question_id (int)
question (text)
answer_1 (varchar)
answer_2 (varchar)
answer_3 (varchar)
etc...
Answers Table
answer_id (int)
question_id (int)
answer (int) The answer they chose. (1, 2, 3, etc.)
How would/should I tally up the results?
Edit: using MySQL, PHP. The difference between the answers (45% said blah, 50% said blah blah开发者_运维技巧, 5% said bloch). Not for homework, Im EE, not CS
If the number of answers is not known beforehand, it would be simpler to split the questions table into 2 - one for the questions (question_id, question_text), and one for choices (question_id, choice_id, choice_text). The answers table could be made into (question_id, answer_id, choice_id). Then selecting it would be using something like the following (QID = the ID of the question you're selecting):
SELECT choice,
(COUNT(*) / (SELECT COUNT(*)
FROM answers
WHERE answers.question_id = QID)) * 100 AS percentage
FROM choices
INNER JOIN answers
ON choices.choice_id = answers.choice_id
AND choices.question_id AND choices.question_id
WHERE choices.question_id = QID
GROUP BY choice_id;
All this does is count the total number of answers in the inner query, then for each choice, divide the number of answers with that choice over the total.
This will select popularity of each answer within the question:
SELECT question,
COUNT(*) /
(
SELECT COUNT(*)
FROM answers ai
WHERE ai.question = a.question
)
FROM answers a
GROUP BY
question, answer
This will not selects the answers if they had never been given.
select answer, count(*) from Answers where answer_id = 1 group by answer
or
select answer_id,answer, count(*) from Answers group by answer_id, answer
I'd give you a query that counts the answers to each question, but sadly you don't have a way to relate the answers to the questions. Nor should you have answer_1, answer_2, etc in the questions table. You need to normalize tha table. If you don't understand how to do it, you need to learn before designing a database.
精彩评论