Avoid to nest aggregation functions in PostgreSQL 8.3.4

Question

Assuming that my subquery yields a number of rows with the columns (x,y), I would like to calculate the value avg(abs(x-mean)/y). where mean effectively is avg(x).

select avg(abs(x-avg(x))/y) as incline from subquery fails because I cannot nest aggregation functions. Nor can I think of a way to calculate the mean in a subquery while keeping the original result set. An avgdev function as it exists in other dialects would not exactly help me, so here I am stuck. Probably just due to lack of sql knowledge - calculating the value from the result set in postprocessing is easy.

Which SQL construct could help me?

Edit: Server version is 8.3.4. No window functions with WITH or OVER available here.

Answer 1

Not sure I understand you correctly, but you might be looking for something like this:

SELECT avg(x - mean/y)
FROM (
  SELECT x, 
         y, 
         avg(x) as mean over(partition by your_grouping_column) 
  FROM your_table
) t

If you do not need to group your results to get the correct avg(x) then simply leave out the "partition by" using an empty over: over()

Answer 2

One option I found is to use a temporary table:

begin; 
  create temporary table sub on commit drop as (...subquery code...);
  select avg(abs(x-mean)/y) as incline from (SELECT x, y, (SELECT avg(x) FROM sub) AS mean FROM sub) as sub2;
commit;

But is that overkill?