Select Top with aggregate function
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
Mike P wrote:
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?
|