Difficulty in handling the query

Question

Difficulty in handling the query

Navigation

#1 by (1 votes)

0

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 this result:

NowIneedtogetthestudent'svariousanswersandcomparethemtogenerateatablewithhisaverage.

Example:

RA|Student|Average
111|Fulano|5

IhavetriedtocomparetheRA's(whichareuniqueidentifiers),savetheequalsinanarrayandthencomparetheanswers,butitdidnotwork,becauseitisnotsavingthesamestudentsinthisarray.

NOTE:Thenumberofstudentresponsescanvary,soeachstudentcanhave1to5answersdependingonthesubject,butinaquery,therewillalwaysbethesamenumberofresponsesperstudent.

Tablelayout:

Can anyone help me and give me a glimpse of how I can do this?

php mysql array

asked by anonymous 06.12.2016 / 21:07

1 answer

Error in Ajax request Submit on numeric keypad - Intel XDK

score 1 · Accepted Answer

You can generally use:

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 answer is correct, it will result in 1 and will not result in 0.

SUM(...)

It will add all the data generated previously, adding all "1", thus obtaining all the correct answers.

COUNT(id)

It will count the number of responses that exist.

* 10

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

Edited:

In your case, I think 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;