# Thread: Average of middle 90%

1. Registered User
Join Date
Sep 2007
Posts
2

## Unanswered: 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?

2. Registered User
Join Date
Nov 2002
Posts
272
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)

3. Registered User
Join Date
Sep 2007
Posts
2
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. :-)

4. Registered User
Join Date
Nov 2002
Posts
272
Originally Posted by miffo
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.

5. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12
Does this work?
Code:
```SELECT Avg(x.myField)
FROM  (SELECT TOP 90 PERCENT FROM myTable ORDER BY someField) As [x]```

6. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
george, come on man, slow down

the required sequence is

1. think
2. post

not the other way round!!!

7. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12
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?

8. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
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%

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Provided Answers: 1
Originally Posted by miffo
I have a set of values that I need to get the average of but only from the middle 90%.
...and later:
Originally Posted by miffo
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.

10. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•