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

Databases
1

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.

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

2

@WebMachine,

Can you help me?

3

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

4

@r937,

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

5

sure… aggregate functions ignore nulls

6

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