We need to query the DB for students whose points are greater than the average for the uni they go to. Now, in the handbook, it is used with GROUP BY clause, but I fail to understand why one is needed here at all if the universities are already grouped in the inner query’s WHERE condition. Could you someone please shed some light on this matter?
SELECT snum, stu_name, uni_name, points FROM Students INNER JOIN Applications a1 USING (snum) WHERE points > (SELECT AVG(points) FROM Students INNER JOIN Applications a2 USING (snum) WHERE a2.uni_name = a1.uni_name GROUP BY uni_name);
GROUP BY is not needed and I wouldn’t recommend using it. Just use:
SELECT s.snum, s.stu_name, a.uni_name, points FROM Students s INNER JOIN Applications a USING (snum) WHERE points > (SELECT AVG(points) FROM Students s2 INNER JOIN Applications a2 USING (snum) WHERE a2.uni_name = a.uni_name );
In this case, the two are equivalent. But if you make a mistake, then using
GROUP BY can return multiple rows — and your query results in an error.
I would also suggest that you qualify all column names and give all tables meaningful aliases.