CODEWITHSUNDEEP
mysqlsubquerycorrelated-subquery
Matt
Matt

Reputation: 1041

Manipulating Results From Two Subqueries in MySQL

For a homework assignment, I have to write a MySQL query to calculate the GPA of every student in the database table. I broke the problem down into 3 parts: (1) calculating the number of grade points earned by each student, (2) calculating the number of credits taken, and then (3) dividing grade points by credits. Here are the queries I've written for steps 1 and 2:

  1. Calculate grade points earned: 

    SELECT ID, SUM( credits ) AS credits_taken
FROM takes
NATURAL JOIN course 
GROUP BY ID

2 Find grade points earned:

    SELECT ID, SUM( credits * ( SELECT points FROM gradepoint WHERE letter = grade ) ) AS tot_grade_points
    FROM takes NATURAL JOIN course
    GROUP BY ID

I manually evaluated each query and they return the correct results. But I can't figure out how to return (credits_taken / tot_grade_points) for each student. Here is what I have tried:

    SELECT ID, GPA
    FROM student AS S NATURAL JOIN
            (SELECT ID,( 'credits_taken' / SUM( credits * ( SELECT points FROM gradepoint WHERE letter = grade ) )) AS GPA
             FROM takes AS T1 NATURAL JOIN course
             WHERE S.ID = T1.ID
             AND EXISTS (
                           SELECT ID, SUM( credits ) AS 'credits_taken'
                           FROM takes AS T2 NATURAL JOIN course
                           WHERE S.ID = T2.ID
                           GROUP BY ID
                          ) 
             GROUP BY ID) Z
    GROUP BY ID

But this gives me the error " Unknown column 'S.ID' in 'where clause'". From what I've read, you can't reference the alias of a table from a subquery in a join operation. Does anyone have another way of doing the calculation of these two subqueries and returning them bound to the student ID?

The 'takes' table maps student IDs to information about the courses they've taken, most importantly the course_id and grade. The 'course' table contains the 'credits' field, the number of credits the course is worth.

EDIT

Here are the relevant table structures:

takes:

    Field     | Type         | Null | Key | Default | Extra |
   +-----------+--------------+------+-----+---------+-------+
   | ID        | varchar(5)   | NO   | PRI |         |       |
   | course_id | varchar(8)   | NO   | PRI |         |       |
   | sec_id    | varchar(8)   | NO   | PRI |         |       |
   | semester  | varchar(6)   | NO   | PRI |         |       |
   | year      | decimal(4,0) | NO   | PRI | 0       |       |
   | grade     | varchar(2)   | YES  |     | NULL    |       |
   +-----------+--------------+------+-----+---------+-------+

course:

   +-----------+--------------+------+-----+---------+-------+
   | Field     | Type         | Null | Key | Default | Extra |
   +-----------+--------------+------+-----+---------+-------+
   | course_id | varchar(8)   | NO   | PRI |         |       |
   | title     | varchar(50)  | YES  |     | NULL    |       |
   | dept_name | varchar(20)  | YES  | MUL | NULL    |       |
   | credits   | decimal(2,0) | YES  |     | NULL    |       |
   +-----------+--------------+------+-----+---------+-------+

Upvotes: 1

Views: 2274

Answers (1)

piotrm
piotrm

Reputation: 12356

I would try:

SELECT takes.sec_id, 
  SUM( course.credits * gradepoint.points ) / SUM( course.credits ) AS GPA
FROM takes
JOIN gradepoint ON takes.grade = gradepoint.letter
JOIN course ON takes.course_id = course.course_id
GROUP BY takes.sec_id

Since your table structure description is incomplete I had to guess gradepoint schema and I assumed sec_id identifies a student in takes table, if there is another column for that just replace it in the query in both SELECT and GROUP BY parts. Maybe it is ID, but a column name like that is usually used for primary keys. Or maybe there are no primary keys defined at all, which is a bad practise anyway. Also you would need to join student table if you wanted any student info other than id, like name and so on.

I would also recommend using JOIN ... ON ... syntax instead of NATURAL JOIN, not only it is more readable, it also gives you more flexibility, for example see how gradepoint is joined instead of using costly dependent subquery.

Upvotes: 1

Related Questions