How do I get my subquery to exclude students that don't have a scholarship to not be included in the result grid?

The question that I have is how can I get my subquery to exclude students who don’t have a scholarship from calculating in the average scholarship? I tried the IS NOT NULL and even tried to put another WHERE Scholarship > 0 in the SELECT statement that is for the subquery. That doesn’t help. Any suggestions or articles or comments would be much appreciated. Here is my sample code with a pic of result grid. Also, when I run this SQL code below, it does output the all the students’ scholarships to be greater than the average scholarship, but the problem is how do I really know if the subquery calculated some students who doesn’t receive one? If it does, it would reduce the average scholarship number.

    Student.LastName AS 'Last',
    Student.FirstName AS 'First',
    Major.Name AS 'Major',
    CONCAT(' ', FORMAT(Scholarship, 2)) AS 'Scholarship'
        INNER JOIN
    Major ON Student.ID = Major.ID
    Scholarship > (SELECT AVG(Scholarship)  FROM Student) 
ORDER BY Student.LastName;


Can you help me?

AVG is an aggregate function and ignores NULLs, so you’re okay


Do you have an online reference to this? I just would like to read more about it.

sure… aggregate functions ignore nulls

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.