Adding a count to a complex SQL query

sql, sql-server-2005

Solution

If I correctly understand your model, just adding this should get you the number of times a question was anwsered:

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

then just join like you did with G and get TotalAnswers. It's quite simple... so there is a good chance that I'm missing something :)

Problem

I have the following query that returns test questions, possible answers to those questions and the number of times each possible answer was selected by the user: ``` 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 ``` The only thing I need is to sum the number of responses to each question but I am really stumbling with this. I will be counting the number of responses and getting the percentage of times a particular response was chosen by the user. Result set: ``` ---- ---- ---- -- --------------------------------------------------------------------------- - ------------ ---- 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 ```

Original source