Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/forums/database-forum/)
-   -   Group By Query - Count Multiple Choice Answers (http://www.go4expert.com/forums/query-count-multiple-choice-answers-t21305/)

jklose 12Mar2010 07:03

Group By Query - Count Multiple Choice Answers
I'm trying to count answers to a series of multiple questions but can't get the SQL query to work properly.

I have a table as follows:

Q1 | Q2 | Q3
Agree | Disagree | Strongly Agree
Disagre | Agree | Agree
Agree | Strongly Agree | Disagree

I would like to output totals for each type of response for each question in a (query) table like:

Answer | Q1Count | Q2Count | Q3Count
Agree | 2 | 3 | 5
Disagree | 1 | 5 | 3
Strongly Agree | 4 | 3 | 2

I've been trying a query like this:

SELECT AnswersTable.Q1, Count(AnswersTable.Q1) AS Q1Count, Count(AnswersTable.Q2) AS Q2Count, Count(AnswersTable.Q3) AS Q3Count
FROM AnswersTable
GROUP BY AnswersTable.Q1
ORDER BY AnswersTable.Q1 DESC;

However, the output from this query is in the wrong order. While the counts for Q1 match the GROUP BY unique values, the other counts are disordered.

Any tips on how to get the right output? Cheers.

All times are GMT +5.5. The time now is 13:55.