Results 1 to 10 of 10
  1. #1
    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. #2
    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. #3
    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. #4
    Join Date
    Nov 2002
    Posts
    272
    Quote 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. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does this work?
    Code:
    SELECT Avg(x.myField)
    FROM  (SELECT TOP 90 PERCENT FROM myTable ORDER BY someField) As [x]
    George
    Home | Blog

  6. #6
    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!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

  8. #8
    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%
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote 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:
    Quote 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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    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
  •