Select Top with aggregate function

[Mike P]

I have a SQL statement that returns an count and average amount paid for a
customer in a table. I wish to alter the SQL so I only get the first X rows
returned. The SQL below is working to return all records.
SELECT Count(*) AS Expr1, Avg(tblPayment.amountPaid) AS Expr2
FROM tblPayment
WHERE (((tblPayment.Customer)="Doe") AND ((tblPayment.amountPaid) Is Not
Null));

I am currently getting 8 records returned for this query since that is how
many are in the data base. When I add TOP 5 to the query, I still get a
count() of 8 returned. Any ideas how to fix this?

SELECT TOP 5 Count(*) AS Expr1, Avg(tblPayment.amountPaid) AS Expr2
FROM tblPayment
WHERE (((tblPayment.Customer)="Doe") AND ((tblPayment.amountPaid) Is Not
Null));

I tried adding a GROUP BY and that returned the 5 records, but it no longer
returned an AVG or a COUNT. It actually returned 5 records. I want the
count() and avg() of the top 5 records. Not the top 5 records.

Any ideas?

 

[John Spencer]

You will have to select the TOP 5 records AND THEN perform the count and
average.

SELECT TOP 5 AmountPaid
FROM TblPayment
WHERE Customer="Doe" AND amountPaid Is Not Null
ORDER BY AmountPaid Desc

Now using that query
SELECT Count(*) as RecordsReturned, Avg(AmountPaid) as TheAverage
FROM TheTop5Query

You may be able to do that all in one query.
SELECT SELECT Count(*) as RecordsReturned, Avg(AmountPaid) as TheAverage
FROM
(SELECT TOP 5 AmountPaid
FROM TblPayment
WHERE Customer="Doe" AND AmountPaid Is Not Null
ORDER BY AmountPaid Desc) as Top5

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County