CODEWITHSUNDEEP
sqlsql-serveraggregate-functionsaverage
user1253073
user1253073

Reputation: 414

Apply aggregate function on subset of rows

I have a table that I want to calculate the average of one column but only for the last 10 rows. 

SELECT AVG(columnName) as avg FROM tableName

I cannot apply top directly since this query only returns one row. I need a way to get the latest 10 rows and do the average on them.

Upvotes: 0

Views: 2033

Answers (2)

user330315
user330315

Reputation: 

select avg(columnName)
from (
   select columnName, 
          row_number() over (order by some column desc) as rn
   from tableName
) t
where rn <= 10;

Upvotes: 3

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT AVG(columnName) FROM
(SELECT TOP 10 columnName FROM tableName ORDER BY ColumnWhichHoldsOrder DESC) A

Upvotes: 5

Related Questions