SQL问卷结果统计问题：如何正确计算频次与占比？

期望的正确结果 #

description   frequency  percentage
is too slow   1          .5
is too fast   1          .5
is just right 0          0
I don't know  0          0

当前错误结果 #

description   frequency  percentage
is too slow   2          1.0000
is too fast   2          1.0000
is just right 2          1.0000
I don't know  2          1.0000

原查询语句 #

SELECT Question_Choices.Choice_description, count(answers.Seq_num) as frequency, (count(answers.Seq_num)/sum(answers.Seq_num)) as percentage
FROM Question_Choices
INNER JOIN answers ON Question_Choices.qid=answers.qid
group by Question_Choices.Choice_description;

表结构 #

Answers(Seq_num, Choice_id, qid)
- 主键(Seq_num, Choice_id, qid)
- 外键Seq_num关联Responce_Person(Seq_num)
- 外键(Choice_id, qid)关联Question_Choices(Choice_id, qid)

Question_Choices(choice_id, Choice_text, qid)
- 主键(Choice_id, qid)
- 外键qid关联Questions(qid)

修正后的查询语句 #

SELECT 
    qc.Choice_description AS description,
    COUNT(a.Seq_num) AS frequency,
    CASE 
        WHEN total.total_responses = 0 THEN 0 
        ELSE COUNT(a.Seq_num) / total.total_responses 
    END AS percentage
FROM Question_Choices qc
LEFT JOIN answers a ON qc.qid = a.qid AND qc.Choice_id = a.Choice_id
CROSS JOIN (
    SELECT COUNT(DISTINCT Seq_num) AS total_responses 
    FROM answers 
    WHERE qid = [替换为你的目标问卷ID]
) AS total
WHERE qc.qid = [替换为你的目标问卷ID]
GROUP BY qc.Choice_description, total.total_responses;

核心修正点 #

1. 补全关联条件：原查询仅通过qid关联两张表，导致每个选项都匹配该问卷的所有回答，统计出错误的高频数。新增qc.Choice_id = a.Choice_id，确保每个选项只统计对应选择的应答记录。
2. 改用LEFT JOIN：原INNER JOIN会过滤掉无应答的选项，改用LEFT JOIN可以保留所有预设选项，无应答时frequency自动显示0。
3. 修正百分比计算逻辑：原查询用sum(answers.Seq_num)计算分母完全错误，正确的分母应该是该问卷的总应答人数（去重后的Seq_num数量）。通过子查询先统计总应答数，再用当前选项的应答数除以总应答数得到百分比。
4. 处理除零异常：通过CASE语句判断总应答数为0的情况，直接返回0，避免出现除法报错。

内容的提问来源于stack exchange，提问作者N3rdAl3rt