开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜