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?

shawnt00 · Accepted Answer · 2012-07-08T07:05:03.267

2

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

edited Jul 08 '12 at 07:05

answered Jul 08 '12 at 06:51

shawnt00

12,316
3
14
19

If you want the average mark across all students then remove the WHERE clause. – shawnt00 Jul 08 '12 at 07:39

score 0 · Answer 2 · answered Jul 08 '12 at 06:48

0

Try this:

Select s.sname, (m.mark - temp.avg)
From marks m
Inner join (select studentnum, avg(mark) as avg from marks group by studentnum) temp 
On temp.studentnum = m.studentnum
Inner Join students s
On s.snum = m.studentnum

answered Jul 08 '12 at 06:48

jjathman

12,156
7
27
33

Wouldn't group by studentnum find average across each student? Not the average of the whole mark from marks table? – user1509652 Jul 08 '12 at 07:21
@user1509652, you are correct and my answer did the same thing. My answer is also easy to change. – shawnt00 Jul 08 '12 at 07:41

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

2 Answers2