选择人口大于 410000 的城市计数

Question

我是SQL的新手。 我在表中有这样的数据

district    city    state   population
d1          c1       s1      2000
d2          c1       s1      10000
d3          c1       s1      400000
d1          c2       s2      500000

我想统计人口大于 410000

的城市所以我想要的输出应该是 2，因为有两个城市 C1 和 C2。我想首先按城市分组，然后计算每个城市的人口总和，然后检查其人口是否大于 410000 所以我使用了查询

select count(city) from city_table group by city having sum(population) > 410000;

但是我得到的输出是

count(city)
3
1

请告诉我的查询中出了什么问题

Answer 1

应使用两个级别的聚合：

select count(*)
from (select city, sum(population) as population
from city_tabl
group by city
having sum(population) > 410000
) c;

Answer 2

MSSQL 查询

select count(Distinct(city)) from city_table where population > 410000;

Answer 3

我认为这会在MySQL中有所帮助：

SELECT COUNT(POPULATION) FROM CITY WHERE POPULATION > 410000;