Counting Survey Results

[cfquest]

I have a survey that has 10 questions and each question is answered with a rating of 1 - 10. On the results page, I need to display a count of each rating for each question (i.e. Q1 = 4 - 1's, 3 - 2's, 5 - 7's, etc). I'm able to get a count but it doesn't show which count is for which rating. The following code is what I'm using:

<!--- Query to collect all results --->
<cfquery name="qQuesResults" datasource="dsn">
SELECT Q1, Q2, Q3, A4, A5, Q6, Q7, Q8, Q9, Q10
FROM mytable
</cfquery>

<cfquery name="cQuesResults" dbtype="query">
SELECT Q1, Count(*) AS cQ1
FROM qQuesResults
GROUP BY Q1
</cfquery>


----------------------------------------------------------------------------------------------------------------
AND how would I display the results output? Right now I'm just using the following:
<cfoutput query="cQ1" group="Q1">#cQuesResults.cQ1#<br></cfoutput>


Also, I'm just testing with the first question (Q1) , but when I do add the others, would I have to create a Q o Q for each Question?

Any help will be appreciated. Thanks

[Dan_Bracuk]

This would be a lot easier with a more normalized db design.

[cf_dev2]

I agree. The beginning of a better design would be to store each question and answer combination in separate rows. That would make your query much easier.