How to return the top 5 values in a query?

[theunknownknight]

I have the following simple query in a database. I returns the code, the number of times the code is in the table, total amount, and average amount. I was wondering how I could modify this query to return just the top five codes with regards to amount. In other words, the query returns this:

code Count TotalAmount AvgAmount
333 4 $94,021.00 $23,505.25
4 1 $34.00 $34.00
422265 1 $3,432.00 $3,432.00
444 1 $345.00 $345.00
45769 1 $7,876.00 $7,876.00
80001 2 $4,743.00 $2,371.50


Here is the SQL:

SELECT Codetable.code, Count(CodeTable.Code) AS Count, Sum(CodeTable.Amount) AS TotalAmount, Avg(CodeTable.Amount) AS AvgAmount
FROM CodeTable
GROUP BY CodeTable.Code;

Is the some way I could write the SQl so that I would return the exact same information but only the top 5 amounts?

Thanks,

Jason

 

[cmmrfrds]

SELECT TOP 5 Codetable.code, Count(CodeTable.Code) AS Count, Sum(CodeTable.Amount) AS TotalAmount, Avg(CodeTable.Amount) AS AvgAmount
FROM CodeTable
GROUP BY CodeTable.Code;

You might need to add an order by

 

[mdthornton]

You can limit the result returned by a query using the top values property. I've added an order by clause so that you get the top 5 amounts:

SELECT TOP 5 Codetable.code, Count(CodeTable.Code) AS Count, Sum(CodeTable.Amount) AS TotalAmount, Avg(CodeTable.Amount) AS AvgAmount
FROM CodeTable
GROUP BY CodeTable.Code
ORDER BY Sum(CodeTable.Amount) DESC;
Best Regards,
Mike