"Trimmed" Average

[sedgely]

i have a query that calculates an average value from a column of integers,however i now need to "trim off" the top and bottom say, 2% of the values and calculate the average of the remaining values. is this possible?
i have the follwoing which trims off the minimum and maximum values, but i need to use a percentage rather than just min and max. Hope this makes sense

AVGTurnRound = CASE WHEN CAST(COUNT(*)-2 AS FLOAT) = 0 THEN 0 ELSE
convert(decimal(10,2),(sum(turnroundtime)-min(turnroundtime)-max(turnroundtime))/cast(count(*)-2 as float))/100 END

Cheers, Craig
Si fractum non sit, noli id reficere

 

[druer]

If you really need to eliminate the specific percentage of rows you could do something like the following:

declare @myResults TABLE (id int identity(1, 1), Turnroundtime)

insert into @myResults
select Turnroundtime
from Table
order by turnroundtime

Now you have a table variable @myResults that has all of your values plus a "row number" -- the id -- So you can get a count of the table and select with a where clause

select Avg(turnroundtime)
from @myResults
where id > (2% of total) and id < (total - 2%)

 

[Kalin]

Why not use the top xx percent clause ??

Select top 96 percent * from table where id-column not in (select top 2 percent id-column from table)



Grtz,

Kalin

 

[vongrunt]

This requires one extra correlated query/derived table.

Post entire query here (*** fingers-crossed it is not 76786 lines long ***).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)