向复杂的SQL查询添加计数

Question

我有以下查询，它返回测试题、这些问题的可能答案以及用户选择每个可能答案的次数：

SELECT  p.program_id, 
        p.pre_survey_form_id, 
        p.post_survey_form_id, 
        fq.form_id, 
        sq.question_id, 
        sq.question_text, 
        qo.question_option_id, 
        qo.option_text, 
        G.Total

FROM    dbo.program p
        LEFT OUTER JOIN dbo.form_question fq
            ON p.pre_survey_form_id = fq.form_id OR p.post_survey_form_id = fq.form_id
        LEFT OUTER JOIN dbo.survey_question sq
            ON fq.question_id = sq.question_id
        LEFT OUTER JOIN dbo.question_option qo 
            ON sq.question_id = qo.question_id
        LEFT OUTER JOIN (
            SELECT ra.question_id, ra.question_option_id, COUNT(*) AS Total
            FROM dbo.form_response_answers ra
            GROUP BY ra.question_option_id, ra.question_id 
        ) G
            ON G.question_id = sq.question_id AND G.question_option_id = qo.question_option_id

ORDER BY p.program_id, fq.form_id, sq.question_id, qo.question_option_id

我唯一需要做的就是把每个问题的回答数加起来，但我真的搞错了。我将计算响应的数量，并获得用户选择特定响应的次数的百分比。

结果集：

----  ----  ----  --  ---------------------------------------------------------------------------  -  ------------  ----
1000  1001  1000  10  How many days a week do you drink at least eight glasses (64 oz.) of water?  1  Never         1
1000  1001  1000  10  How many days a week do you drink at least eight glasses (64 oz.) of water?  2  Once          1
1000  1001  1000  10  How many days a week do you drink at least eight glasses (64 oz.) of water?  3  Two times     NULL
1000  1001  1000  10  How many days a week do you drink at least eight glasses (64 oz.) of water?  4  Three times   2
1000  1001  1000  10  How many days a week do you drink at least eight glasses (64 oz.) of water?  5  Four times    NULL
1000  1001  1000  10  How many days a week do you drink at least eight glasses (64 oz.) of water?  6  Five or more  NULL

Answer 1

如果我正确理解了你的模型，只要添加这个就可以得到一个问题被回答的次数：

 LEFT OUTER JOIN (
            SELECT ra.question_id, COUNT(*) AS TotalAnswers
            FROM dbo.form_response_answers ra
            GROUP BY ra.question_id 
        ) G2

然后像G一样加入并获得TotalAnswers。很简单..。因此，我很可能遗漏了一些东西:)