Member Avatar for xxreenaxx1

I am working on a rule to find out which question has the highest correct answer. Not sure how to work with this. But here is an explanation of what I have so far and what I want my program to do.

I have question and answer table
Question: questionid,QuestionName, Questionchoice1,Questionchoice2, Questionchoice3, Questionchoice4,answer1,answer2,answer3,answer4
Answer: answerid, Answer1,Answer2,Answer3,Answer4,questionid

Question table is where the admin will add question,choice for these and the answers.
Answer table will have answers to these question that were taken by students.

So now what I want to do is, get all the answer for each question and find out which question was answered correctly the most.

  • 3 Contributors
  • 2 Replies
  • 88 Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by Akash Saikia

Dani AI

Generated

: the simplest, most reliable approach is to store questions, each possible choice, and students' answers in separate rows rather than as many columns. was right to ask about student ids — they are needed to avoid double counting and to group answers by student. correctly flagged the denormalized multiple-answer columns; move to three tables (questions, choices with a boolean is_correct, and student_answers linking student -> question -> choice). That lets SQL aggregate correctly and keeps data integrity.

Example query to find the question with the most correct answers (counts distinct students who chose a correct choice):

SELECT q.id, q.text, COUNT(DISTINCT sa.student_id) AS correct_count
FROM questions q
JOIN choices c ON c.question_id = q.id AND c.is_correct = 1
JOIN student_answers sa ON sa.choice_id = c.id
GROUP BY q.id, q.text
ORDER BY correct_count DESC
LIMIT 1;

If using MySQL 8+ and you want to return all top questions in case of a tie, use a window function (RANK) over the grouped counts.

Practical tips: add indexes on student_answers(question_id, choice_id, student_id) and on choices(question_id, is_correct). Enforce a unique constraint when one answer per student per question is required (or use an attempt_id for repeated tries). Count DISTINCT student_id when students can change answers to avoid inflated counts. Store is_correct on choices, not in student answers, to preserve auditability.

Recommended Answers

All 2 Replies

Member Avatar for urtrivedi

where is student id, how will you know, that who answered what?

Edited by urtrivedi because: n/a
Member Avatar for Akash Saikia

Why do you have multiple fields for storing one question's answer??
like you have answer1,answer2,answer3 and answer4...

do you want to provide multiple answers to be true for one question?

Please clarify this. :)

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.