if a teacher rate a student1 "4" and two teacher rate student2 "2" and "2" thus both have same ratings
How is it possible???
Try below query:
DECLARE @tt TABLE ([Teacher_Id] INT, [Student_Id] INT, [Rating] INT, [Date] DATETIME)
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,1,2,'2013-01-02')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,1,2,'2013-01-03')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,2,4,'2013-01-04')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,3,5,'2013-01-05')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,3,1,'2013-01-06')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,4,4,'2013-01-07')
INSERT INTO @tt ([Teacher_Id], [Student_Id], [Rating], [Date])
VALUES(1,4,4,'2013-01-08')
SELECT [Student_Id], [Rating]
FROM @tt
SELECT [Student_Id], AVG([Rating]) AS AVERAGE
FROM @tt
GROUP BY [Student_Id]
Results:
1. query
Student_Id Rating
1 2
1 2
2 4
3 5
3 1
4 4
4 4
2. query
Student_Id Average
1 2
2 4
3 3
4 4