Well, definitely not the prettiest thing I've ever written, but it at least works with the data you have. I have no idea how it would scale on a much larger dataset. Give it a try and let me know what you think.

with yearCounts as

(

select DATEPART(year, CourseDate) CourseYear, COUNT(*) GradeCount

from EStats

group by DATEPART(year, CourseDate)

),

qOrder as

(

select *, ROW_NUMBER() over (partition by y.CourseYear order by e.Grade) rn

from EStats e

join yearCounts y on y.CourseYear = DATEPART(year, e.CourseDate)

)

select q.CourseYear

, SUM(case when q.GradeCount % 2 = 1 then case when q.rn = (q.GradeCount+1)/2 then q.Grade else 0 end

else case when q.rn in ((q.GradeCount/2),(q.GradeCount/2)+1) then convert(decimal(4,1), q.Grade)/2 else 0 end end) Median

, SUM(case when q.rn = q.GradeCount then q.Grade else 0 end) [Max]

, SUM(case when q.rn = 1 then q.Grade else 0 end) [Min]

, SUM(case when q.GradeCount % 2 = 1 then case when ((q.GradeCount - 1)/2) % 2 = 1 then

case when q.rn = (((q.GradeCount-1)/2)+1)/2 then q.Grade else 0 end

else case when q.rn in ((((q.GradeCount-1)/2)/2),(((q.GradeCount-1)/2)/2)+1) then convert(decimal(4,1), q.Grade)/2 else 0 end end

else case when (q.GradeCount/2) % 2 = 1 then case when q.rn = ((q.GradeCount/2)+1)/2 then q.Grade else 0 end

else case when q.rn in (((q.GradeCount/2)/2),((q.GradeCount/2)/2)+1) then convert(decimal(4,1), q.Grade)/2 else 0 end end end) LowerQ

, SUM(case when q.GradeCount % 2 = 1 then case when ((q.GradeCount - 1)/2) % 2 = 1 then

case when q.rn = q.GradeCount - ((((q.GradeCount-1)/2)+1)/2) + 1 then q.Grade else 0 end

else case when q.rn in (q.GradeCount - (((q.GradeCount-1)/2)/2) + 1,q.GradeCount - ((((q.GradeCount-1)/2)/2)+1) + 1) then convert(decimal(4,1), q.Grade)/2 else 0 end end

else case when (q.GradeCount/2) % 2 = 1 then case when q.rn = q.GradeCount - (((q.GradeCount/2)+1)/2) + 1 then q.Grade else 0 end

else case when q.rn in (q.GradeCount - ((q.GradeCount/2)/2) + 1,q.GradeCount - (((q.GradeCount/2)/2)+1) + 1) then convert(decimal(4,1), q.Grade)/2 else 0 end end end) UpperQ

from qOrder q

group by q.CourseYear