Jump to content


Photo

mysql subqueries and math


  • Please log in to reply
1 reply to this topic

#1 emehrkay

emehrkay
  • Staff Alumni
  • Advanced Member
  • 1,214 posts

Posted 06 April 2006 - 02:26 PM

i have a query to calculate the total number of questions in a test and i can use the same query with an added clause to calculate the total number of correct answers on that test.

how do i combine the two queries into one that would output correct/total?

thanks

SELECT
COUNT(qo.question_option_id) AS total from
answer a
INNER JOIN
class_test ct ON a.class_id = ct.class_id AND a.test_id = ct.test_id AND a.test_type_id = ct.test_type_id
INNER JOIN
test_question tq ON ct.test_id = tq.test_id AND a.question_id = tq.question_id
INNER JOIN
question_option qo ON tq.question_id = qo.question_id AND a.question_id = qo.question_id
WHERE
a.test_type_id = 2
AND
a.class_id = 72
AND
a.participant_person_id = 1

i could simply add AND qo.question_option_correct = 1 to get the number of correct questions

#2 emehrkay

emehrkay
  • Staff Alumni
  • Advanced Member
  • 1,214 posts

Posted 06 April 2006 - 03:15 PM

i got it
SELECT
                    ((  SELECT
                            COUNT(qo.question_option_id) AS correct
                        FROM
                            answer a
                        INNER JOIN
                            class_test ct ON a.class_id = ct.class_id AND a.test_id = ct.test_id AND a.test_type_id = ct.test_type_id
                        INNER JOIN
                            test_question tq ON ct.test_id = tq.test_id AND a.question_id = tq.question_id
                        INNER JOIN
                            question_option qo ON tq.question_id = qo.question_id AND a.question_id = qo.question_id
                        WHERE
                            a.test_type_id = 2
                        AND
                            a.class_id = ?
                        AND
                            a.participant_person_id = ?
                        AND
                            qo.question_option_correct = 1
                        )/(
                        SELECT
                            COUNT(qo.question_option_id) AS total
                        FROM
                            answer a
                        INNER JOIN
                            class_test ct ON a.class_id = ct.class_id AND a.test_id = ct.test_id AND a.test_type_id = ct.test_type_id
                        INNER JOIN
                            test_question tq ON ct.test_id = tq.test_id AND a.question_id = tq.question_id
                        INNER JOIN
                            question_option qo ON tq.question_id = qo.question_id AND a.question_id = qo.question_id
                        WHERE
                            a.test_type_id = 2
                        AND
                            a.class_id = ?
                        AND
                            a.participant_person_id = ?
                        ) * 100
                    ) AS matery
                FROM
                    class_enrollment ce
                WHERE
                    ce.class_id = ?
                AND
                    ce.person_id = ?





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users