Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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 4:34am
Comments
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: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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.
 
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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 
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,055
1 DamithSL 4,621
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,500
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 20 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100