Aggregate function "AVG()" doesn't support "SELECT TOP 5" qu

[fletchsod]

I'm a bit surprised when I discover this recently... It wasn't made known to me for 8 years til now.

When you do this query...

SELECT TOP 5 PurchasePrice from tblPurchase 
WHERE Year = '2008' 
AND Make = 'Ford' 
AND Model = 'Mustang'

Instead of 80 rows, I get the 1st 5 rows which works. When you do this average...

SELECT AVG(PurchasePrice) from tblPurchase 
WHERE Year = '2008' 
AND Make = 'Ford' 
AND Model = 'Mustang'

It average all 80 rows and I got $12,284 (PurchasePrice). But when I do this...

SELECT TOP 5 AVG(PurchasePrice) from tblPurchase 
WHERE Year = '2008' 
AND Make = 'Ford' 
AND Model = 'Mustang'

I still get $12,284 meaning it used all 80 rows instead of 5 rows.

Why is that? What's wrong with this sql-query?

Thanks...

 

[bborissov]

Nothing, just when you use AGGREGATE function the query runs, and after that TOP 5 is executed.
Your query generates just ONE record .
So use Derived tables:

SELECT AVG(PurchasePrice) AS Test
FROM (
SELECT TOP 5 PurchasePrice from tblPurchase
WHERE Year  = '2008'
  AND Make  = 'Ford'
  AND Model = 'Mustang'
ORDER BY ?????????????) Tbl1

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[fletchsod]

Ah! I assumed from writing this sql-query that that top 5 would run first then AVG() would run second. I guess that's why people say SQL is not smart enough or worse, I'm not smart enough.

Thanks...