SQL “more than one row returned by a subquery used as an expression” error

name | grade
------------
Sam  | 4
Sam  | 5
Sam  | 3
Mark | 1
Mark | 2
John | 3
Alice| 1
Alice| 2

I have this database and I want to select the people who’s average grade is higher than the average grade (overall). For example, for Sam, it will check whether “(4+5+3)/3 > (4+5+3+1+2+3+1+2)/8” is true and if it is, then it will return Sam.

SELECT * 
FROM grade 
WHERE (SELECT DISTINCT AVG(grade) from grade GROUP BY name) > (SELECT AVG(grade) from grade);

I tried this but I keep getting an error “more than one row returned by a subquery used as an expression”… Am I using the WHERE statement wrong?

Answer

The Error message you are getting is related to this below part of your script-

(SELECT DISTINCT AVG(grade) from grade GROUP BY name)

This above script actually returning one row per student and in your case it is 4 row as there are 4 student in total. Now, by normal logic, you can not implement a WHERE clause like more than one value like (1,23,200) > ‘Some Value’.

GROUP BY name is a bad option as more than one student can have the same name. You can go with student id instead.

Finally, you can try this below logic with HAVING in place of WHERE.

SELECT name,AVG(grade)
FROM grade 
GROUP BY name
HAVING AVG(grade) > = (SELECT AVG(grade) from grade)

To get the count of student, you can use this below logic with subquery-

SELECT COUNT(*) FROM
(
    SELECT name,AVG(grade) average
    FROM grade 
    GROUP BY name
    HAVING AVG(grade) > = (SELECT AVG(grade) from grade)
)A