Group By Report Question

[peeldb]

Hi all,

I have a simple table that stores survey response records. 3 questions, 1 comment field, and a record id. The answers in each question column can be from 1 to 5.

recordid | q1 | q2 | q3
1 4 2 1
2 5 4 5
3 3 2 3

I would like to write a query that counts the number of responses for each possible answer, but for each question of the survey.

For computing the totals on one column I can use this:

SELECT Q1 AS AnswerValue, Count(Q1) AS NumAnswers
FROM Results
GROUP BY Q1
HAVING Q1<>"";

I want to compute these totals for each column, but I can't think of the code required. Any suggestions?

 

[PHV]

Take a look at union query:
SELECT "Q1" AS Question, Q1 AS AnswerValue, Count(Q1) AS NumAnswers
FROM Results WHERE Q1<>"" GROUP BY Q1
UNION SELECT "Q2", Q2, Count(Q2)
FROM Results WHERE Q2<>"" GROUP BY Q2
UNION SELECT "Q3", Q3, Count(Q3)
FROM Results WHERE Q3<>"" GROUP BY Q3
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[peeldb]

Thanks a lot! I was looking for a way to do it with one simple SELECT statement, and I wasn't sure if it was possible. Thanks for your solution.

- Jake