query for sum of max values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query for sum of max values

Author

Topic

sadiakhattak
Starting Member

4 Posts

Posted - 2011-06-05 : 15:35:24

hi
i want to write a query for finding sum of maximum values. i have a folowing table with folowing columns
semesterid studentid subjectid subject-grade

i want to find sum of all subjects for each semester which is obtained with the folowing query
"select sum(subject_grade) from marks group by studentid,semesterid,subjectid"
the problem is if a student repeats the course then the maximum marks for the that subject either it was in 1st atempt or 2nd would be included. e.g.
student a has following record
semester subject subjectgrade
1 A 2.5
1 B 3
1 A 3.3 (repeated)
2 C 3
2 D 4

so i want my sum to be 3+3.3+3+4=13.3 rather than 15.8. how would i get that?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-05 : 16:28:11

If you want the sum of all subjects for each semester, wouldn't you rather want to group only by studentid and semesterid as in


elect sum(subject_grade) from marks group by studentid,semesterid

Regardless, if you want to pick only the highest grade for a given subject in a given semester (question: can someone get two grades for a given subject in a single semester?) one way to do it is as follows:


;with a as
( 
select 
   studentid, semesterid, subjectid, subjectgrade,
   row_number() over (partition by semsterid,subjectid order by grade desc) as rownum
from
   marks
)
select studentid,semesterid, sum(subjectgrade) from a
where rownum = 1
group by studentid, semesterid

sadiakhattak
Starting Member

4 Posts

Posted - 2011-06-06 : 14:22:58

yup subjectid wasnt required. n for a repeated subject ofcourse u cant have 2 grades for a single subject in a semester n u cannot include both the grades while calculating gpa. i wanted this to calculate semester gpas ie sum of subjects offered in a particular semester / sum of credit hours. so even a person repeats the course in some other semester i stil need max of that subject.
anyways thnx u soooooooo much for ur help i got what i wanted. GOD bless u
tc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources