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

Question

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_id	student_name
77	Pepe LePew

student_courses

student_id	course_id	percent_grade
77	101	95.7
77	202	85.9
77	303	77.1
77	404	66.6

grades

from_percent	to_percent	letter_grade	GPA
0	69	F	0
70	72	C-	1.67
73	76	C	2.0
77	79	C+	2.33
80	82	B-	2.67
83	86	B	3.0
87	89	B+	3.33
90	92	A-	3.67
93	96	A	4.0
97	100	A+	4.0

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

student_id	GPA
77	2.67

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 

Answer 1

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

student_id	student_name	GPA
69	Bart McEndree	4
77	Pepe LePew	2.67