Help with Query

[wvoke9]

Hi,

I am a complete novice with MySQL but have been persevering with it for a project I am doing at work. I have created a results server to analyse and collect data I receive from quizzes I have created using QuestionWriter HTML5.

I have figured out how to run a query and have a vague understanding of the 'language' used.

The questions I have created are multiple choice, I want to be able to run a query on a specific question so i can see how many people have answered a, how many people answered b, etc. I have used the following query which I believe is half of the solution:

SELECT 'QuestionReference','Response'
FROM qw5questiondata
WHERE 'QuestionReference' LIKE ...............

......... is just whatever the specific question reference is.

This query displays to me every single persons answer with the question reference repeated. What I want is a two column table which shows me the answer and how many times it has been chosen. So for 20 people the result of the query might look like.

A 10
B 5
C 2
D 1

I hope that is clear! If anyone can suggest a query for me to use or just some advice would be great.

Thanks in advance,

Will

[pritaeas]

You'll need to use GROUP BY so you can use COUNT. If you can show how your table and data looks we can be more specific.

[wvoke9]

Yeh I thought it would be a combination of groupby and count. The data is in a table which looks like this

    Question Reference    Response        Response Summary
      Question 1               a             ...........
      Question 2               b             ...........
      Question 1               c             .........
      Question 3               a             ...........
      Question 2               a             ..........
      Question 1               d             ..........

There are additional columns but these are irrelevant. What I want is to be to look only at question 1 and find out how many people answered a,b c, or d. Ideally the response summary would be shown too as it tells me what the response a actually was.

I hope this is the information you required to help!?

Cheers,

Will

[pritaeas]

SELECT `QuestionReference`, `Response`, COUNT(*) AS `ResponseCount`, `ResponseSummary`
FROM qw5questiondata
GROUP BY `QuestionReference`, `Response`
ORDER BY `QuestionReference`, `Response`

[wvoke9]

Hi, I tried the coding you suggested and only ended up getting the total number of responses. Do I need to use something like.

SELECT QuestionReference LIKE 'Question 1', Response, COUNT(a,b,c,d) , ResponseSummary
FROM qw5questiondata
GROUP BY 'Response'

I think thats what I want apart from the count bit I think is wrong?

[wvoke9]

Well I know it's wrong I just tried it! Everything I try I always get a total count of the responses, rather than the individual response count, maybe DISTINCT will do a job somehow??

[pritaeas]

Do you mean this:

SELECT `Response`, COUNT(*) AS `ResponseCount`, `ResponseSummary`
FROM qw5questiondata
WHERE QuestionReference = 'Question 1'
GROUP BY `Response`

[wvoke9]

Thnak you very much that has solved it!

[wvoke9]

I will be using this query repeatedly. Is there a way to save me having to type it in every time? Thanks for all your help, you have saved me hours of time!

[pritaeas]

I will be using this query repeatedly

Make it a stored procedure.