空间查询子查询没有功能

Question

我正在尝试向下面添加一个子查询，以计算每个县的人口-我可以运行代码sperate并得到正确的答案，尽管当我尝试将其合并到现有查询中时，它会返回该国的人口；

所以这个代码单独工作很好

select a.name, sum(b.totadult)
from admin.oas_centroids_stats as b
join admin.councils as a
on st_contains(a.geom, b.geom)
group by a.name;

单独代码输出

这也是我试图添加的查询；

select a.name, count(b.distname), round(st_area(a.geom) / 1000000), round((st_area(a.geom) / 1000000)/(count(b.distname))),
(select sum(c.totadult) from admin.oas_centroids_stats as c join admin.councils as a on st_contains(a.geom, c.geom))
from admin.councils as a
join map.railwaystations as b on st_contains(a.geom, b.geom)
group by a.name, st_area(a.geom);

电流输出

如有任何帮助，我们将不胜感激！

Answer 1

您在选择子查询中缺少分组，因此是完整人口，而不是每个国家的人口。

如果你想用子查询完成这项工作，下面类似的东西应该会给你想要的东西。

select a.name, count(b.distname)
, round(st_area(a.geom) / 1000000)
, round((st_area(a.geom) / 1000000)/(count(b.distname)))
, c.cnt as population
from admin.councils as a
join map.railwaystations as b on st_contains(a.geom, b.geom)
join (select a.name, sum(b.totadult) as cnt
from admin.oas_centroids_stats as b
join admin.councils as a
on st_contains(a.geom, b.geom)
group by a.name) as c on c.name =  a.name 
group by a.name, st_area(a.geom);

但是您应该能够使用窗口函数来实现这一点，这将是一种更好的方法。