postgresql - 如何在 where 子句中使用 sum 来计算平均值

Question

我对 sql 还是个新手，我刚刚使用 postgresql 一个星期。我有点卡住了..

我正在使用纽约数据集 ( http://workshops.opengeo.org/postgis-intro/about_data.html )。

我必须找到一种方法来列出亚裔人口多于相应行政区平均值的所有社区，并且我必须显示亚裔人口的百分比。

我想过这个:

select name, 100 * sum(c.popn_asian)/(select (avg(popn_asian)) from nyc_census_blocks group by boroname)
from nyc_neighborhoods n, nyc_census_blocks c
where (ST_Contains(n.geom, c.geom) = true )
and (sum(c.popn_asian)) > (select avg(popn_white) from nyc_census_blocks c1 group by c1.boroname) 

但我显然不太愿意在 where 子句之后使用 sum ......我想知道为什么并学习如何解决这个问题。

谢谢

Answer 1

select name, 100 * sum(c.popn_asian)/(select (avg(popn_asian)) from nyc_census_blocks group by boroname)
from nyc_neighborhoods n, nyc_census_blocks c
where ST_Contains(n.geom, c.geom) = true 
group by name
having sum(c.popn_asian) > (select avg(popn_white) from nyc_census_blocks c1 group by c1.boroname)