Using the SUM() in the ON Clause of the Join

I have 3 tables named students, student_courses and grades. And for each student I need to calculate their overall GPA. So I'm a bit stuck. :(

students table

student_courses

grades

Here's my query that is getting an error. (Error Code 1111. Invalid use of group function) I happen to be using MySQL but would be happy to get a standard SQL solution.

SELECT student_id, student_name,
(select g.GPA      
 from course_student AS cs    
 inner join Grades AS g ON (sum(cs.percent_grade) / count(*)) BETWEEN g.from_percent AND g.to_percent
 where cs.student_id = students.id) As GPA 
FROM students 

The expected result would be a listing of all of the students in the student table and their corresponding overall GPA

Update 1.

I just got it to work for a single student. But I need it to work for all of the students in the students table. Here's the code for a single student.

select g.GPA, g.from_percent, g.to_percent
 from course_student AS cs    
 inner join Grades AS g 
 where cs.student_id = 77
 group by g.GPA, g.from_percent, g.to_percent
 HAVING (sum(cs.percent_grade) / count(*)) BETWEEN g.from_percent AND g.to_percent 

Answers

To calculate the overall GPA for each student, you can use a combination of joins and aggregate functions. Here's how you can modify your query to achieve this:

SELECT 
    s.student_id, 
    s.student_name,
    AVG(g.GPA) AS overall_GPA
FROM 
    students s
JOIN 
    student_courses sc ON s.student_id = sc.student_id
JOIN 
    grades g ON sc.percent_grade BETWEEN g.from_percent AND g.to_percent
GROUP BY 
    s.student_id, s.student_name;

Explanation:

• We join the students, student_courses, and grades tables based on the appropriate keys (student_id and percent_grade).
• Then, we calculate the GPA for each course by matching the percent grade to the corresponding GPA range in the grades table.
• Next, we group the results by student_id and student_name.
• Finally, we use the AVG() function to calculate the overall GPA for each student.

This query should give you the expected result, listing all students along with their overall GPAs.