Take the 2-minute tour ×

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.

share|improve this question
I suppose the question is... if you really, really need this behavior... is there a reason you can't get an upgrade pushed through? –  xenoterracide Aug 25 '10 at 12:54
when depesz does his 'waiting on ' series he sometimes shows the previous way of doing it... you might want to look up his waiting on window functions or similar. –  xenoterracide Aug 25 '10 at 12:56
That would be this one then: depesz.com/index.php/2009/01/21/waiting-for-84-window-functions - thanks for that pointer. –  relet Aug 25 '10 at 13:18
re: upgrade - besides changing a running system in production and the time it takes - no. It is an option. –  relet Aug 25 '10 at 13:23
add comment

3 Answers

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

SELECT avg(x - mean/y)
  SELECT x, 
         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()

share|improve this answer
select x, y, avg(x) as mean over() from (..my subquery here..) as sub; fails with an ERROR: syntax error at or near "over" for me. –  relet Aug 25 '10 at 11:20
Window functions seems to be a 8.4 feature, and the server I am querying is 8.3.4. Thanks anyway. :] –  relet Aug 25 '10 at 11:46
That's one of the reaons you should always mention your Postgres version ;) –  a_horse_with_no_name Aug 25 '10 at 13:13
I will learn. ;) –  relet Aug 25 '10 at 13:19
add comment

if your data sets are not too large you could accumulate them into an array and then return the incline from a function:

create type typ as (x numeric, y numeric);

create aggregate array_accum( sfunc = array_append,
                              basetype = anyelement,
                              stype = anyarray,
                              initcond = '{}' );

create or replace function unnest(anyarray) returns setof anyelement 
                           language sql immutable strict as $$
  select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;$$;

create function get_incline(typ[]) returns numeric 
                language sql immutable strict as $$
  select avg(abs(x-(select avg(x) from unnest($1)))/y) from unnest($1);$$;

select get_incline((select array_accum((x,y)::typ) from subquery));

sample view for testing:

create view subquery as 
select generate_series(1,5) as x, generate_series(1,6) as y;
share|improve this answer
add comment
up vote 0 down vote accepted

One option I found is to use a temporary table:

  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;

But is that overkill?

share|improve this answer
You don't need the temporary table, just put the subquery code at the same place where you have the table –  a_horse_with_no_name Dec 8 '10 at 17:37
add comment

Your Answer


