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.

Cop

"I would like to avoid subselects if possible". Why? – Maroney

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

Demasculinize

But then for each row returned, I get a different z value, and the same average value. This is not the behaviour I want; for each z value, I want to get a corresponding average of sums particular to that value of z. For instance, if my data is x y z 10 a g 15 a g 7 b g 8 a h 12 b h 18 b h I would expect to have returned z, average g 16 h 19 – Cop

My original SQL was syntactically incorrect. It should have had "z" as a grouping variable on the inner subquery. I just fixed that. – Demasculinize

Answer 2

You could also use a Common Table Expression (CTE) for that:

WITH tmp AS (
    SELECT y, z, sum(x) as sumval
    FROM t
    GROUP BY y, z
)
SELECT z, avg(sumval)
FROM tmp
GROUP BY z

Metalinguistic

Answer 3

use over() clause:

SELECT y, AVG(SUM(x)) OVER(partition by y) WHERE ... GROUP BY ...;

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16

Mugwump