Thanks Kyle, this worked for me. However, I have one more doubt. In the table question_states - event column, some of the entries I find are entered as 3 & some are entered as 6. I went throught the Question Bank Schema link which you mentioned in the last post. It is mentioned there at event 3 equals to 'Grade' & event 6 equals to 'Close&Grade'. Can you tell me what is the difference between the two of them.
I have two moodle sites, say moodle A & moodle B. Quizzies are there & students have appeared & submitted them. In site A, the events in the question_states is only 3. No event = 6 is there, while in site B, only event=6 is there & no event=3. Since, Grades are marked only against event 3 & 6, I am a bit confused which event to select. Here is a query which I am using to find out how many have scored correct & how many wrong for a particular question:
SELECT st.grade, count( * )
FROM mdl_question_states st
JOIN mdl_question_sessions sess ON st.question = sess.questionid
AND st.attempt = sess.attemptid
JOIN mdl_quiz_attempts quiza ON quiza.uniqueid = sess.attemptid
WHERE quiza.id >0
AND quiza.quiz =1 /*specific quiz ID */
IN ( 3, 6 )
AND st.question =302 /*specific question ID */
GROUP BY st.grade
LIMIT 0 , 30
This query counts no. of users who have scored 0 or 1, since Grades are marked 1 for a right answer & 0 for a wrong answer. I cannot use both (3,6) coz if it happens that both events 3 & 6 are marked for a particular question, the Count parameter will get repeated & thus the outcome will not be accurate. So I want to make sure which event number to use in my case.