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.

"*I would like to avoid subselects if possible*". Why? – Jun 19 '12 at 21:29 — , Jun 19 '12 at 21:29

Gordon Linoff · Accepted Answer · 2012-06-20T14:30:32.297

25

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

edited Jun 20 '12 at 14:30

answered Jun 19 '12 at 19:08

Gordon Linoff

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` – ferson2020 Jun 20 '12 at 14:27
1

My original SQL was syntactically incorrect. It should have had "z" as a grouping variable on the inner subquery. I just fixed that. – Gordon Linoff Jun 20 '12 at 14:30

score 0 · Answer 2 · answered Oct 13 '21 at 12:33

0

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

answered Oct 13 '21 at 12:33

andschar

score 0 · Answer 3 · answered Jul 04 '22 at 06:43

0

use over() clause:

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

answered Jul 04 '22 at 06:43

M2E67

3 Answers3