Search code examples
sqlsql-serverinner-joinaverage

How to get average of values in T-SQL from specific table and join it's result with other one?

So basically, I have two tables:

  • STUDENT {IdStudent Int (PK), IndexNr Varchar(10), Year Int, Name Varchar(32), Surname Varchar(64)}
  • MARK {IdMark Int (PK), IdStudent Int (FK), Value Int, Subject Varchar(32)}

and as a first part of this exercise I need to run over them and grab all students from specific year (let's 2) and write out their average mark separately for each one of them.

I wanted to do this through cursor but I'm having troubles with it. Mainly because I can't properly select these average marks using inner join.

For example I would like to return IndexNr and AverageMark.

Solution

  • You can do it by joining the tables and then use group by each student:

    select 
  s.idstudent, s.name, s.surname
  avg(m.Value) AverageMark
from student s inner join mark m
on m.idstudent = s.idstudent
where s.year = 2019
group by s.idstudent, s.name, s.surname

    but shouldn't the table mark also contain a year column?