Combining 3 output from 3 queries in mysql and do some calculations

[mikeyoung]

I have two tables

Table 1 -mdl_question_attempts            

 

id      questionid    rightanswer    responsesummary

1       1                        A                          A

2       1                        A                          B

3       1                        A                          A

4       1                        A                          B

5       2                        A                          A

6       1                        A                          A

7       2                        D                          E

8       2                        D                          D

9       2                        D                          E

10     3                        F                          F

11       3                                 F                                   G

 

 

Table 2 - mdl_question_attempt_steps

 

id          questionattemptid        userid

5           1                                            1

6           2                                            1

7           3                                            2

8           4                                            1

9           5                                            2

10         6                                            1

11         7                                            1

12         8                                            1

13         9                                            1

14         10                                         1

15         11                                         1

 

 

 

 

Table 1 -mdl_question_attempts, primary key –“id” field is related with

Table 2 - mdl_question_attempt_steps , foreign key –“questionattemptid”

Table 1 is about users answers for certain questions.

“rightanswer”-is the correct answer for a particular question and “responsesummary” is the answer given by user for that question.  “questionid” represent the question no.  Sometimes same user tried one question several times and their answer in each attempts shows in table 1.

For each question  “userid” or user can be found from Table 2

Eg:1^st row in table1 done by userid =1

So my question is I want to find percentage or ratio of times a learner(one user-eg:userid =1)  answers the same question twice wrong, based on the number of times a learner answered a question twice?

Highlighted ones in the table 1 shows the userid=1  related data

User1  answered question 1 – 4 times and it is 2 times wrong

User1  answered question 2 – 3 times and it is 2 times wrong

Question 3 is answered 2 times and only 1 time wrong. So I want same question twice wrong. Therefore

Question 3 is not considered

 

questionid       wrong count

 

1                    2/4

2                    2/3

 

So my final output for the userid=1 is

=((2/4)+(2/3))/2

=0.583

 

=summation of wrong count divided by average or that is 2 times (only 2 questioned answerd) If 3 question answered summation should be divided by 3.

 

I wrote following three codes and I can get the output separately. But I want to get this in one query

 

function quiztwicewrong()

{

                $con=mysqli_connect("localhost:3306","root","", "moodle");

                // Check connection

                if (mysqli_connect_errno())

                {

                                echo "Failed to connect to MySQL: " . mysqli_connect_error();

                }

                //quiz twice wrong

 

                //query 1                                            

                $resultq = mysqli_query ($con,"SELECT  mdl_question_attempts.rightanswer as rightanswer,count(mdl_question_attempts.questionid) as questionid1 FROM mdl_question_attempts,mdl_question_attempt_steps WHERE  mdl_question_attempt_steps.questionattemptid=mdl_question_attempts.id and mdl_question_attempt_steps.userid='1'  and mdl_question_attempts.rightanswer<>mdl_question_attempts.responsesummary GROUP BY mdl_question_attempts.questionid HAVING questionid1>1 ") or die("Error: ".     mysqli_error($con));

                while($rowq= mysqli_fetch_array( $resultq))

                {

                                echo $rowq['questionid1']."-".$rowq['rightanswer']."<br>"."<br>"."<br>";

                }

                                //query 2            

$resultqall = mysqli_query ($con,"SELECT  mdl_question_attempts.rightanswer as rightanswer,count(mdl_question_attempts.questionid) as questionid1 FROM mdl_question_attempts,mdl_question_attempt_steps WHERE  mdl_question_attempt_steps.questionattemptid=mdl_question_attempts.id and mdl_question_attempt_steps.userid='1'  GROUP BY mdl_question_attempts.questionid HAVING questionid1>1") or die("Error: ".     mysqli_error($con));

                while($rowqall= mysqli_fetch_array( $resultqall))

                {

                                echo $rowqall['questionid1']."-".$rowqall['rightanswer']."<br>"."<br>"."<br>";

                }

               

                                //query 3            

                $resultqdup = mysqli_query ($con,"SELECT count(*) as duplicate FROM

                                                (select mdl_question_attempts.rightanswer as rightanswer from mdl_question_attempts,mdl_question_attempt_steps WHERE  mdl_question_attempt_steps.questionattemptid=mdl_question_attempts.id and mdl_question_attempt_steps.userid='1'  and mdl_question_attempts.rightanswer<>mdl_question_attempts.responsesummary GROUP BY mdl_question_attempts.questionid HAVING COUNT(mdl_question_attempts.questionid)>1) as questionid1 ") or die("Error: ".     mysqli_error($con));

                while($rowqdup= mysqli_fetch_array( $resultqdup))

                {

                                echo $rowqdup['duplicate'];

                }

               

                mysqli_close($con);

}

return quiztwicewrong();

 

Outputs from the 3 queries are

 

query 1- ouput

2-A

2-D

 

query 2- ouput

4-A

3-D

2-F   (I don’t want this part-this comes for the 3^rd question, but  I want only the output related to query 1- ouput,only answer more than 1 time wromg)

 

query 3- ouput

2

 

So I want to combine 3 output and need to calculate and get the value

=((2/4)+(2/3))/2

=0.583

Please help me to do this by editing my code or any suggestion please?

 

 

 

 

 

 

 

 

 

 

 

 

[Barand]

Whey the second table? You could have put the userid as a column in the first table>

 

try

SELECT ROUND(SUM(incorrect/answered)/COUNT(*), 3) as result
FROM
    (
    SELECT qa.questionid, 
    SUM(IF(qa.rightanswer <> qa.responsesummary, 1, 0)) as incorrect ,
    COUNT(*) as answered
    FROM mdl_question_attempts qa
        INNER JOIN mdl_question_attempt_steps qas
        ON qa.id = qas.questionattemptid
    WHERE qas.userid = $user
    GROUP BY qa.questionid
    HAVING incorrect > 1
    ) as totals

OUTPUT:

+----------+
|  result  |
+----------+
|  0.583   |
+----------+

Edited August 19, 2013 by Barand

[mikeyoung]

 

hi Barand. Thank you very much for your answer. I solved  it with your help.

 

 

[jazzman1]

O, my master! One more amazing sql select statement. Forever stay your fan. I like that

Edited August 20, 2013 by jazzman1