How do I compare two queries by two columns in MySQL?

What's the best way to compare two queries by two columns? these are my tables:

This table shows exam questions

idEvaluation | Question | AllowMChoice | CorrectAnswer|
1                1            0             3 
1                2            1             4
1                2            1             5
1                3            0             9

This table shows a completed exam

  idExam| idEvaluation | Question |开发者_如何学运维 ChosenAnswer|
    25        1              1            2
    25        1              2            4
    25        1              2            5
    25        1              3            8      

I have to calculate the percentage of correct Answers, considering to certain questions may allow multiple selection.

Correct Answers / Total Answers * 100

thanks for your tips!

---

This code will show you a listing of Questions and whether or not they were answered correctly.

select
    A.Question,
    min(1) as QuestionsCount,
    -- if this evaluates to null, they got A) the answer wrong or B) this portion of the answer wrong
    -- we use MIN() here because we want to mark multi-answer questions as wrong if any part of the answer is wrong. 
    min(case when Q.idEvaluation IS NULL then 0 else 1 end) as QuestionsCorrect
from
    ExamAnswers as A
    left join ExamQuestions as Q on Q.Question = A.Question and Q.CorrectAnswer = A.ChosenAnswer
group by
    A.Question -- We group by question to merge multi-answer-questions into 1

Output Confirmed:

Note, the columns are intentionally named this way, as they are to be included as a subquery in part-2 below.

---

This code will give you the test score.

select
    sum(I.QuestionsCorrect) as AnswersCorrect,
    sum(I.QuestionsCount) as QuestionTotal,
    convert(float,sum(I.QuestionsCorrect)) / sum(I.QuestionsCount) as PercentCorrect -- Note, not sure of the cast-to-float syntax for MySQL
from
    (select
        A.Eval,
        A.Question,
        min(1) as QuestionsCount,
        min(case when Q.idEvaluation IS NULL then 0 else 1 end) as QuestionsCorrect
    from
        ExamAnswers as A
        left join ExamQuestions as Q on Q.Question = A.Question and Q.CorrectAnswer = A.ChosenAnswer
    where 
        A.Eval = 25
    group by
        A.Question, A.Eval) as I
group by        
    I.Eval

Output Confirmed:

This will communicate the general concept. Your column names idEvaluation and Eval are difficult for me to understand, but I'm sure you can adjust the code above to suit your purposes.

Note, I did this in sql server, but I used fairly basic SQL functionality, so it should translate to MySQL well.