Subtracting a value in a column by average of the same column without using a variable in ISQL

Question

I have 2 tables: Marks (studentnum,marks) Student (SNum, SName)

if I do

Select SName, marks-avg(marks) from Marks join Student on SNum = studentnum

then I only get 1 row returned.

Is there a way to return all the list of students' names and the difference of the student's mark and the average (student's mark - average) without assigning a variable for average?

Answer 1

You're wanting to mix aggregate and non-aggregate values. This uses a subquery, but there might be other options depending on your server.

SELECT
    SName,
    marks - (SELECT avg(marks) FROM Marks as m2 WHERE m2.studentnum = m.studentnum)
FROM
    Marks as m INNER JOIN
    Student as s
        ON s.SNum = m.studentnum