Get average value from the "last" N rows in a table

Question

In my Postgres database table I can find the last 20 entries of cap_cs137 by date order:

select cap_cs137 FROM capintec ORDER BY cap_date DESC LIMIT 20;

I can also get the average:

select avg(cap_cs137) FROM capintec LIMIT 20;

However, how do I combine the above statements to get the average value for the last 20 entries by date order?

We have an outdated RHEL 5.8 server which supports Postgres 8.1.23 and therefore does not allow use of WITH queries.

Answer 1

You can use a CTE:

WITH s AS
    (SELECT cap_cs137 FROM capintec ORDER BY cap_date DESC LIMIT 20)
SELECT avg(cap_cs137) FROM s;

The first query becomes cached as a temporary table "s", then we run the aggregate over it in the final query.

Edit:

It turned out the OP could not use a CTE since he is using an older version of Postgres, so the final answer was a sub-select (which I happen to like less than a CTE purely for readability; but does the exact same thing in this case):

SELECT avg(cap_cs137) FROM
   (SELECT cap_cs137 FROM capintec ORDER BY cap_date DESC LIMIT 20);

Answer 2

Just use an inner select:

SELECT AVG(cap_cs137)
FROM (SELECT cap_cs137 FROM capintec ORDER BY cap_date DESC LIMIT 20) AS sub;