Get grade from max section - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Get grade from max section

Author

Topic

giszzmo
Starting Member

29 Posts

Posted - 2015-05-07 : 16:55:19

Hello,
I am very perplexed on how to get the average grade from a list of students where some students had to take the test twice. So if there is a section 002, I need to average that grade in from the student and not their section 001. I have tried an if/else and case for the section id and also avg for the grades all in the select statement but nothing seems to get what I need. My results so far are:

Student Grade Section
1245 80 001
3658 75 001
2569 65 001
2569 72 002

select Student, Grade , SECTION
from   StudentTable
group by Student, GRADE, SECTION
order by Student

If anyone could point me in the right direction. Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-07 : 17:00:58

Do you want the average of 65 and 72 for student 2569? Or just ignore the 65? I'm confused. Please show us expected output given that sample data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

giszzmo
Starting Member

29 Posts

Posted - 2015-05-07 : 17:04:08

It needs to be the average of 80, 75, and 72 to equal 75.67. So it needs to ignore the grade for 001 if a student also has a grade for 002.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-07 : 17:22:31

with StudentGrade (Student, Grade, MaxSection)
as (
select Student, Grade, MAX(Section) as MaxSection
from YourTable
group by Student, Grade)
select AVG(yt.Grade) as AvgGrade
from YourTable yt
join StudentGrade sg on yt.Student = sg.Student and yt.Grade = sg.Grade and yt.Section = sg.MaxSection

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

giszzmo
Starting Member

29 Posts

Posted - 2015-05-07 : 17:24:34

Thank you! Why did you choose WITH?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-07 : 17:35:13

Why? To solve the problem.

It's just a newer way of writing a derived table, in my mind at least.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources