11,710,423 members (88,597 online)
Rate this:
See more:
i have a table Rating in my project in which i have attribute Teacher_Id (int),Student_Id(int),Rating(int),Date(DateTime);
Teachers will rate their Student , now i want to select id of those student which have highest Rating but i have a problem if a teacher rate a student1 "4" and two teacher rate student2 "2" and "2" thus both have same ratings. I have a table name best student now this i have a problem to how to find the average of student and select best student id based on ratings.
Posted 19-Feb-13 3:34am
Kschuler at 19-Feb-13 9:37am

So in that scenario, which student SHOULD be considered the best?
Sandeep Mewara at 19-Feb-13 9:41am

Not clear.
Kschuler at 19-Feb-13 10:02am

I know, that's why I asked. :)
Sandeep Mewara at 19-Feb-13 10:28am

Oops! Sorry, pressed the wrong button.

My question is for OP.

ryanb31 at 19-Feb-13 10:06am

For average, just use AVG(). For example, SELECT AVG(studentscores) AS averagescore, studentid FROM sometable GROUP BY studentids

Rate this:

## Solution 2

```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:
```--Teacher_Id (int),Student_Id(int),Rating(int),Date(DateTime);

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```
Rate this:

## Solution 1

I won't do the work for you, but I'll give you a push in the right direction:

You can get the averages for each student in conjunction with a GROUP BY clause.

Further hint: if the possibility exists for a student to be listed but as yet unrated by one or more instructors, you need a mechanism to handle this, depending upon the default value of unrated student entries.

Rate this:

## Solution 3

Hi Jackson ,

Please find the below query .

it will help to know about average in SQL server.

`select Name,SUM(marks) as Sum ,AVG(marks) as Avegage from sa  Group By Name `