If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Select Top with aggregate function
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? |
#2
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|