CODEWITHSUNDEEP
postgresqlpsqlpostgresql-8.1
moadeep
moadeep

Reputation: 4098

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

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.

Upvotes: 8

Views: 4124

Answers (2)

zxq9
zxq9

Reputation: 13154

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);

Upvotes: 10

simon
simon

Reputation: 2164

Just use an inner select:

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

Upvotes: 4

Related Questions