如何在sql查询中执行数学运算以计算百分比差异? [英] How would I perform math within a sql query to calculate percent difference?

问题描述

我想进行调查(2个或更多选择)，并将结果放在表格中.我只想知道如何计算sql查询中的结果.

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.

更多，我应该在查询中进行数学运算，还是仅使用PHP进行数学运算?

Even more, should i be doing the math within the query, or just use PHP to do the math?

示例

问题表

question_id (int)
question (text)
answer_1 (varchar)
answer_2 (varchar)
answer_3 (varchar)
etc...

答案表

answer_id (int)
question_id (int)
answer (int) The answer they chose. (1, 2, 3, etc.)

我将/应该如何计算结果?

How would/should I tally up the results?

使用MySQL，PHP.答案之间的差异(45％表示不满意，50％表示不满意等等，5％表示不满意).不是做作业，我是EE，不是CS

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

推荐答案

如果事先不知道答案的数量，将问题表分成2个就更简单了-将一个问题(question_id，question_text)分为一个一个用于选择(question_id，choice_id，choice_text).答案表可以做成(question_id，answer_id，choice_id).然后使用以下类似方法(QID =您选择的问题的ID)进行选择:

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.

这篇关于如何在sql查询中执行数学运算以计算百分比差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！