Prev: Random Function for Selecting Records
Next: Please help with access expression
From: Mike P on 29 May 2010 06:24
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?

From: John Spencer on 29 May 2010 12:48
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?
>
 | 
Pages: 1
Prev: Random Function for Selecting Records
Next: Please help with access expression