CODEWITHSUNDEEP
mysqlsql
Ofek Ron
Ofek Ron

Reputation: 8580

What's wrong with this query using averages?

Courses (cid, cname, description)

Professors (pid, pname)

Teaching (tid, cid, pid, year, semester, department)

Students (sid, lname, fname, b_date, department)

Enrolled_stud (sid, tid, enrollment_date, grade, grade_date)

The Question: query all the students names who have the highest average:

SELECT a.sname,a.avg
FROM (SELECT s1.sname,avg(e.grade)AS avg
      FROM Students s1
      NATURAL JOIN EnrolledStudents e
      GROUP BY s1.sid
      ) as a
where a.avg=(select max(a.avg))

you can see the results i get in my SQLFIDDLE, and you can see that i was doing something wrong, and i dont get what! it all seem correct to me...

Upvotes: 3

Views: 96

Answers (2)

Ofek Ron
Ofek Ron

Reputation: 8580

Well if no one has a better looking answer that works, then that makes this the best answer:

SELECT s.sname
FROM Students s
NATURAL JOIN EnrolledStudents e
GROUP BY s.sid
HAVING avg(e.grade) >= ALL(SELECT AVG(e.grade)
      FROM  EnrolledStudents e
      GROUP BY e.sid)

Upvotes: 0

Rich O'Kelly
Rich O'Kelly

Reputation: 41757

Since it's for study, I'll post this hint first. Think about the where clause - a is the current row. Of course a.avg equals max(a.avg).

Update

Try the following:

SELECT s1.sname, avg(e.grade) AS avg
FROM Students s1
NATURAL JOIN EnrolledStudents e
GROUP BY s1.sid
HAVING avg = (SELECT avg(e.grade) AS avg 
  FROM EnrolledStudents e
  GROUP BY e.sid ORDER BY avg DESC LIMIT 1);

Upvotes: 1

Related Questions