postgresql: 求和后再求平均值

有时候我们会在数据库中按组求和：

select
sum (cast(size as bigint)) as total
from
file
where size != ''
group by creator_id
having sum (cast(size as bigint)) != 0;

结果如下：

 其中cast(size as bigint)为类型强制转化，因为我这里字段类型为vachar并且数据大小超出了integer的表示范围，所以加了强制转换为bigint。并且求和后我想过滤掉和为0的结果，就在后面加了having sum (cast(size as bigint)) != 0。

好了，按组把和求出来了，我们再继续求这些和的平均值：

套用此公式：

select avg(f.total) from (select ...) f;

我这里套用后为：

select
avg(f.total)
from (
        select sum (cast(size as bigint)) as total
        from file where size != ''
        group by creator_id
        having sum (cast(size as bigint)) != 0
     )
f;

结果如下：