Nested aggregated functions with grouping in postgresql

Question

I am trying to get the average number of sums using nested aggregate functions and grouping. I would like to:

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 sum, where each sum is above the rows, where y is the same.

I would like to avoid subqueries 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