Member Avatar for 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

  • 2 Contributors
  • 9 Replies
  • 143 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for 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.

Member Avatar for 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

Member Avatar for pritaeas 
SELECT `QuestionReference`, `Response`, COUNT(*) AS `ResponseCount`, `ResponseSummary`
FROM qw5questiondata
GROUP BY `QuestionReference`, `Response`
ORDER BY `QuestionReference`, `Response`
Member Avatar for 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?

Member Avatar for 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??

Member Avatar for pritaeas

Do you mean this:

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

Thnak you very much that has solved it!

Edited by wvoke9
Member Avatar for 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!

Member Avatar for pritaeas

I will be using this query repeatedly

Make it a stored procedure.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.