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.
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
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
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
use over() clause:
SELECT y, AVG(SUM(x)) OVER(partition by y) WHERE ... GROUP BY ...;
