SQL query problem

Question

I have a SQL table called StudentMarks.which consist of StudentID,SubjectName,SubjectMark

I want to write a stored procedure to retrieve StudentID,TotalSubjectMarks,MarksAverage,ClassPosition for each student.I need to find the ClassPosition from the MarksAverage. for the student who has the highest MarksAverage, ClassPosition should be 1.others should get the position as 2,3,4.... Can any one help me with this???

Thank You.

Answer 1

  SELECT StudentID, TotalSubjectMark, MarksAverage,
  ROW_NUMBER() OVER (ORDER BY MarksAverage DESC) ClassPosition
  FROM (
    SELECT StudentID, 
    SUM(SubjectMark) TotalSubjectMark,
    AVG(SubjectMark) MarksAverage
    FROM StudentMarks sm
    GROUP BY StudentId
  )

Answer 2

Try this :

select * , ROW_NUMBER ()  OVER(ORDER BY avgmarks ) AS ClassPosition from
(
 select 
 studentid, sum(subjectmark) as total  , AVG(subjectmark)   as avgmarks
 from studentmarks  group by studentid
) d

Answer 3

Using sql server 2005+ you can try

DECLARE @StudentMarks TABLE(
        StudentID INT,
        SubjectName VARCHAR(50),
        SubjectMark FLOAT
)

INSERT INTO @StudentMarks SELECT 1, 'A', 80
INSERT INTO @StudentMarks SELECT 1, 'B', 80
INSERT INTO @StudentMarks SELECT 1, 'C', 80
INSERT INTO @StudentMarks SELECT 2, 'A', 60
INSERT INTO @StudentMarks SELECT 2, 'B', 60
INSERT INTO @StudentMarks SELECT 2, 'C', 60


;WITH Marks AS (
        SELECT  StudentID,
                SUM(SubjectMark) TotalSubjectMarks,
                AVG(SubjectMark) MarksAverage
        FROM    @StudentMarks
        GROUP BY StudentID
)
SELECT  *,
        ROW_NUMBER() OVER(ORDER BY MarksAverage DESC) Position
FROM    Marks