Average of middle 90%

Hello all!

This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:
1
1 <-From here
1
2
3
4
4
5
6 <- To here.
7
I thought I could solve it by subqueries and do the following:
Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9
which would give me what I want, but I realised that when aggregating I cant order the subqueries.

This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.

I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?Do some more nesting:
Select sum(yourField) from
(select top 5% yourField from yourTable order desc)
should give you the sum of the bottom 5%.

(untested)|||Hello!

I tried you query but it didnt work. I got an syntax error on From.
I managed to solve it anyhow by using the IN-clause in the WHERE-clause like this:

Select (sum(myfield)-
(select sum(myfield) from mytable Where mytable.id IN
(select top 5 percent mytable.id from mytable Order By myfield ASC))
+
(select sum(myfield) from mytable Where mytable.id IN
(select top 5 percent mytable.id from mytable Order By myfield Desc))
)/(Count(rows)*0.9)
from mytable

But thanks for all you help anyway. Its greatly appreciated. I probably wouldnt have thought of this unless the suggestion I got. :-)|||I tried you query but it didnt work. I got an syntax error on From.
Told you it was untested :)

I'm glad you could use the idea though.|||Does this work?

SELECT Avg(x.myField)
FROM (SELECT TOP 90 PERCENT FROM myTable ORDER BY someField) As [x]|||george, come on man, slow down

the required sequence is

1. think
2. post

not the other way round!!!|||When reading this thread earlier I wrote a working subquery - that was knocked out from memory. I'm assuming your remark means that I've got it wrong?|||while i have not subjected your solution in post #5 to rigorous testing, i just do not see how it can possibly meet the stated objective of averaging the middle 90%|||I have a set of values that I need to get the average of but only from the middle 90%.
...and later:
I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?

Honestly, I don't see why you guys even bother responding to posts from people that can't even ask a straight question.|||Can you restate your problem in "real world" terms instead of trying to describe what you want the code to do? I can correctly answer the question that you've posed at least six different ways, so what I need to help you is a more exact mental image of what you want. If you describe what you want in terms of "real world" actions, I think that I can help you.

-PatP