CODEWITHSUNDEEP
sqlsql-serverinner-joinaverage
crystalsky
crystalsky

Reputation: 87

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:

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.

Upvotes: 0

Views: 47

Answers (3)

Andy Raddatz
Andy Raddatz

Reputation: 2882

I think GROUP BY is what you're looking for: https://www.w3schools.com/sql/sql_groupby.asp

SELECT 
    s.Name
    ,s.Year
    ,AVG(m.Value) as Mean        
FROM Student s
JOIN Mark m ON m.IdStudent = s.IdStudent
GROUP BY
    s.Name
    ,s.Year

Upvotes: 1

forpas
forpas

Reputation: 164089

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?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If you want the average over all students, then you want one row. Don't use group by.

You also need a where for the year condition:

SELECT AVG(s.Value) as Mean
FROM student s
WHERE s.year = ?  -- whatever year you want

Upvotes: 0

Related Questions