Jump to content

Archived

This topic is now archived and is closed to further replies.

emehrkay

mysql subqueries and math

Recommended Posts

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

[code]
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[/code]

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

Share this post


Link to post
Share on other sites
i got it
[code]
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 = ?[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.