Post reply

Aggregate and Top

  • September 17, 2003 at 11:50 am

    #81113

    Is there any way to use aggregate functions with top, ie

    Select avg (top 5 discount)

    From dbo.discounts

    I get syntax error near top. Any work arounds?

    thanks.

  • September 17, 2003 at 12:10 pm

    #474706

    I'm sure there's a more elegant way but one way is to do a select into then an average.

    select top 5 int into ##newtable

    from oldtable

    order by int desc

    select avg(int) from ##newtable

    HTH

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • September 17, 2003 at 12:14 pm

    #474708

    What am I saying...sorry, try this:

    select avg(int) from table

    where int in (select top 5 int from table)

    HTH

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • September 17, 2003 at 12:17 pm

    #474709

    Just having a ball with this one 🙂

    Might give you different results but you could also do

    select top 5 avg(int) from table

    Keep in mind that unless you want the order returned by the clustered index (if there is one) you need to use an order by.

    HTH and I promise my last one 🙂

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • September 17, 2003 at 4:28 pm

    #474767

    ray_higdon:

    thanks for the replies.

    The query

    select top 5 avg(int) from table

    gets the average of the entire table for int field, since the result is only one value the top 5 is redundant. What I am looking for is average of top 5.

    The other two solutions you proposed will work.

    thanks.,

  • September 17, 2003 at 7:02 pm

    #474790

    Yep, you are absolutely right! Glad two of the fifty responses I did worked 🙂

    Ray Higdon MCSE, MCDBA, CCNA

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

  • September 18, 2003 at 7:16 am

    #474864 

    select avg(discount) from (select top 5 discount from discounts) a

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply