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 

>Solution :

We can create a StudentGrades CTE that groups by student and calculates the average grade from all of their courses. Then we can then use AvgGrade to join on the grades table.

WITH StudentGrades as
(
  SELECT s.student_id, s.student_name, SUM(percent_grade) as TotalGrade, count(*) as CourseCount, CAST(SUM(percent_grade) / count(*) AS INT)  as AvgGrade
  FROM students  s
  INNER JOIN student_courses c on s.student_id=c.student_id
  GROUP BY s.student_id, s.student_name
)
SELECT sg.student_id, sg.student_name, g.GPA
FROM StudentGrades sg
INNER JOIN grades g on g.from_percent <= sg.AvgGrade AND g.to_percent >= sg.AvgGrade

fiddle