A question about a correlated subquery in SQL and AVG function

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);

Answer

The 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.