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.
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
)
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
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
