Sorry if I have posted this in the wrong section , as I am new to the site.
The inner query works perfectly and gives me a count of correct and incorrect answers of a quiz for each student, I have a student that is assigned two questions, They have answered one question correctly and the other incorrect.
When I do a count, it shows 2 correct and 2 incorrect.
I have tried various different queries but all end up showing the same. what am I doing wrong?
SELECT Q.firstname, Q.lastname, Q.Class_List_id1, COUNT((CASE WHEN Q.Correct =1 THEN 1 ELSE 0 END)) AS Correct1, COUNT((CASE WHEN Q.Incorrect =1 THEN 1 ELSE 0 END)) AS Incorrect1
FROM
(SELECT Quiz.quiz_id AS Quiz_id1, Quiz.quiz_title, Quiz_Assign.quiz_assign_id, Quiz_Assign.quiz_id AS Quiz_id2, Quiz_Questions.quiz_question_id, Quiz_Questions.quiz_id, Quiz_Questions.question, Quiz_Questions.option1, Quiz_Questions.option2, Quiz_Questions.option3, Quiz_Assign_Student_Question.quiz_question_id AS quiz_question_id1, Quiz_Assign_Student_Question.response, Quiz_Questions.answer, Class_List.Class_List_id AS Class_List_id1, Quiz_Assign.class_list_id,(CASE WHEN Quiz_Assign_Student_Question.response=Quiz_Questions.answer THEN 1 ELSE 0 END) AS Correct,(CASE WHEN Quiz_Assign_Student_Question.response!=Quiz_Questions.answer THEN 1 ELSE 0 END) AS Incorrect, User_Accounts_.firstname, User_Accounts_.lastname, User_Accounts_.user_id
FROM Class_List, Quiz, Quiz_Assign, Quiz_Questions, Quiz_Assign_Student_Question, User_Accounts_
WHERE (Quiz.quiz_id=Quiz_Assign.quiz_id) AND (Quiz_Assign.quiz_id=16) AND (Quiz_Questions.quiz_id=Quiz.quiz_id) AND (Quiz_Questions.quiz_question_id=Quiz_Assign_Student_Question.quiz_question_id) AND (Quiz_Assign_Student_Question.quiz_assign_id=Quiz_Assign.quiz_assign_id) AND (Class_List.Class_List_id=Quiz_Assign.class_list_id) AND (Class_List.class_id=29) AND (User_Accounts_.user_id=Class_List.user_id)) AS Q
GROUP BY Class_List_id1