September 17, 2003 at 11:50 am
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
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
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
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
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
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
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