Using GROUP BY winth JOIN-CodePudding

I am having problems using GROUP BY and JOIN

I have 2 tables: 1. STUDENTS_TO_COURSE with course_id and student_id columns 2. Students with id, name, age columns

What I need to do is to find a course with highest average student age

I was thinking about using join to unite these tables and then to group it somehow

I also understand that I need to use AVG to count average student age on the joined table

select STUDENTS_TO_COURSE.course_id as course_id, 
students.id as student, 
students.age as age 
from STUDENTS_TO_COURSE
join students on STUDENTS_TO_COURSE.student_id = students.id

I was thinking about for for a few days now, but couldn't come up with any idea

Is there any clue how to solve this?

Thanks in advance

CodePudding user response：

Try this:

select STUDENTS_TO_COURSE.course_id as course_id, 
MAX(AVG(age)) as avg_age
from STUDENTS_TO_COURSE
join students on STUDENTS_TO_COURSE.student_id = students.id
group by course_id,age

CodePudding user response：

select STUDENTS_TO_COURSE.course_id as course_id, 
avg(age) as age 
from STUDENTS_TO_COURSE
join students on STUDENTS_TO_COURSE.student_id = students.id group by course_id order by age desc limit 1

CodePudding user response：

If your SQL code is correct, the following gets a list of course IDs and average ages in decreasing order of average age. What you want is the top line of the list.

select 
   STUDENTS_TO_COURSE.course_id as course_id, 
   AVG(students.age) as avg_age
from STUDENTS_TO_COURSE
join students on STUDENTS_TO_COURSE.student_id = students.id
GROUP BY STUDENTS_TO_COURSE.course_id
ORDER BY AVG(students.age) DESC