i am a new sql aspirant and was given with a assignment and struck with it
i have a table like the following
---------------------------------------------------------------
no | studentID | subject | marks
1 001 maths 90
2 001 history 88
3 002 maths 99
4 002 history 88
5 002 computer 97
and i want the output like this
--------------------------------------
no | student name | total | rank
1 james 178 2
2 john 227 1
irrespective of the number of subjects , based on the register number the total should be added dynamically and rank should be produced
What I have tried:
SELECT
m.StudentID as ID,
s.SubjectName as subjects,
m.MarkRate as marks
FROM
Mark m
INNER JOIN Subject s on m.SubjectID = m.SubjectID
ORDER BY
m.StudentID,
s.SubjectName
----------------------------------
SELECT
X.StudentID,
X.StudentName,
ROWNUMBER() OVER ( ORDER BY X.TotalMark desc) as Rank
FROM (
SELECT
m.StudentID,
s.StudentName,
sum(m.MarkRate) TotalMark
FROM
Mark m
INNER JOIN Student s on s.StudentID = m.StudentID
GROUP BY
m.StudentID,
s.StudentName
) X
ORDER BY X.TotalMark desc
i treid this but unable to add values dynamically
also i need to use the UPDATE keyword can use it as trigger too.