Nested aggregate functions with grouping in postgresql

Question

I'm trying to get an average of sums using nested aggregate functions and grouping. What I would want to do is:

SELECT AVG(SUM(x) GROUP BY y) WHERE ... GROUP BY ...;

That is, for each row returned, I want one of the fields to be an average of sums, where each sum is over the rows where y is the same.

I would like to avoid subselects if possible.

Answer 1

You need a subquery:

select z, avg(sumval)
from (select y, z, sum(x) as sumval
      from t
      group by y, z
     ) t
group by z