Difficulty handling the query

Question

Personal I have the following query:

SELECT p.ra, p.nomealuno, 
qa.respostaaluno, q.respostacorreta
FROM prova p
INNER JOIN questoes_aluno qa
ON qa.idprova = p.id
INNER JOIN questao q
ON q.id = qa.idquestao
AND q.iddisciplina = 46
ORDER BY p.id ASC;

And it generates that result:

Now I need to take the student’s various responses and compare them to generate a table with his average.

Example:

RA | Student | Average
111 | So-and-so | 5

I’ve tried comparing the AR’s (which are unique identifiers), storing the same ones in an array and then comparing the answers, but it didn’t work, because it’s not saving the same students in this array.

NOTE: The amount of student responses may vary, so each student may have 1 to 5 responses depending on the subject, but in a query, there will always be the same number of responses per student.

Table Outline:

Can someone help me and shed some light on how I can do this?

Answer 1

You can use in general:

SELECT
  ( ( SUM( IF(respostaaluno = respostacorreta, 1, 0) ) / COUNT(id) ) * 10 ) as Media
FROM
  Respostas
GROUP BY `ra`

See this by clicking here.

Explanation:

IF(respostaaluno = respostacorreta, 1, 0)

If the student’s response is correct it will result in 1 and if it does not result in 0.

SUM(...)

It will add up all the previously generated data, adding up all the "1", thus getting all the hits.

COUNT(id)

Will count the amount of answers that exist.

* 10 

Multiply by 10 to generate an average of 0 to 10 instead of 0 to 1.

Edited:

In your case I believe you could use something similar to this:

SELECT 
p.ra, 
p.nomealuno, 
( ( SUM( IF(qa.respostaaluno = q.respostacorreta, 1, 0) ) / COUNT(qa.id) ) * 10 ) as Media
FROM prova p
INNER JOIN questoes_aluno qa
ON qa.idprova = p.id
INNER JOIN questao q
ON q.id = qa.idquestao
AND q.iddisciplina = 46
GROUP BY p.ra
ORDER BY p.id ASC;