Hello everyone,
I am trying to get a report that shows all students' short answers responses and count right responses, wrong responses and total responses for each question, I've gotten this done except for responses from quizes made by random question based on tags.
this is my query
SELECT
prefix_quiz.name AS "quiz name",
prefix_question.name AS "question name",
fn_RemoveHTMLTag(prefix_question.questiontext) AS "question",
prefix_question_answers.answer AS "answer",
GROUP_CONCAT(
DISTINCT prefix_question_response_analysis.response
) AS "student responses",
SUM(
prefix_question_response_analysis.credit = 1
) AS "right responses",
SUM(
prefix_question_response_analysis.credit = 0
) AS "wrong responses",
COUNT(
prefix_question_response_analysis.credit
) AS "total responses"
FROM
prefix_question
right JOIN prefix_question_answers ON prefix_question.id = prefix_question_answers.question
right JOIN prefix_question_response_analysis ON prefix_question.id = prefix_question_response_analysis.questionid
right JOIN prefix_quiz_slots ON prefix_quiz_slots.questionid = prefix_question.id
right JOIN prefix_quiz ON prefix_quiz_slots.quizid = prefix_quiz.id
WHERE
prefix_question.qtype LIKE "shortanswer"
GROUP BY
prefix_question_response_analysis.questionid
any guides would be really appreciated.