>>>OK, this one shouldn't be too difficult....I had to write this in the past week, and figured it would be a good post for a sql puzzle.
>>>
>>>Suppose you have a table of test scores....
>>>
>>>StudentID TestScore
>>>1 100
>>>2 100
>>>3 95
>>>4 93
>>>5 92
>>>6 90
>>>7 90
>>>8 88
>>>9 81
>>>10 65
>>>11 30
>>>
>>>The average score is about 84: T-SQL has the AVG function, so that's easy.
>>>
>>>But to try to minimize the impact of any outliers, how would you write something to calculate the middle (i.e. median) value?
>>>
>>>For those not familiar with the median calcuation, it's the middle score. In this situation (an odd # of rows, sorted by testscore desc), it would be the sixth (middle) row. Had the table contained an even # of rows (e.g. 12 rows), it would be the straight average of rows 6 and 7.
>>>
>>>So, how would you write something (fairly generic) to calculate the median score? (No googling, please) <s>
>>
>>I would give the simplest T-SQL answer using the program logic (not a set based logic)
>>
>>declare @TotalStudents int
>>
>>select @TotalStudents = count(*) from StudentsScore
>>
>>if @TotalStudents%2 = 1
>>
>> select TestScore from (select *, row_number() over (order by ID) as Row from StudentsScore) X where Row = @TotalStudents/2 + 1
>>else
>> select avg(TestScore) from (select TestScore from (select *, row_number()
>>over (order by ID) as Row from StudentsScore) X where Row = @TotalStudents/2
>> union all TestScore from (select *, row_number() over (order by ID) as Row from StudentsScore) X
>> where Row = @TotalStudents/2 + 1) Y group by TestScore
>>
>>
>>This is from the top of my head - not tested.
>>
>>I may come up with a better answer later. BTW, I added ROW_NUMBER() in case we have gaps in sequence for some reason. If we don't, we can use straight selects based on ID.
>
>
>I don't know why you would order by Id
>
>But what about this
>
>declare @StudentScore table (Id int, Score int)
>
>insert into @StudentScore
>select 1, 100
>union all select 2, 100
>union all select 3, 95
>union all select 4, 93
>union all select 5, 92
>union all select 6, 90
>union all select 7, 90
>union all select 8, 88
>union all select 9, 81
>union all select 10, 65
>union all select 11, 30;
>
>
>
>select avg(Score) as Median
> from ( select Id,
> Score,
> ROW_NUMBER() over (order by Score) Rank,
> @@ROWCOUNT as Number
> from @StudentScore
> ) xxx
> where ( Rank in ((Number+1)/2, (Number+2)/2) )
>
Yes, should be by Score, my bad.
If it's not broken, fix it until it is.
My Blog