列'city.POPULATION‘在选择列表中无效，因为它既未包含在聚合函数中，也未包含在GROUP BY子句中

Question

我正在创建一个查询，我看到了这个错误消息。这是我试图执行的查询：

select D.ID_city
,      D.ID_year
,      D.NB_deaths
,      D.NB_births_M
,      D.NB_births_F
,      D.NB_population_AS
,      D.NB_population_ACL
,      COUNT( NB_births_M+ NB_births_F)/C.population as  Birthrate
,      COUNT(NB_deaths_M + NB_deaths_F) as Mortality
from   Demography D 
join   region C
on     (D.Id_city =C.Id_city)
group
by     D.ID_city
,      D.ID_year
,      D.NB_deaths_M
,      D.NB_births_M
,      D.NB_births_F
,      D.NB_population_AS
,      D.NB_population_ACL

Answer 1

您应该在group by中包含c.population，因为您在count聚合函数之外使用该列：

COUNT( NB_births_M+ NB_births_F)/C.population

使用这个(也添加了D.NB_deaths_F，因为它也是必要的)：

select D.ID_city
,      D.ID_year
,      D.NB_deaths
,      D.NB_births_M
,      D.NB_births_F
,      D.NB_population_AS
,      D.NB_population_ACL
,      COUNT( NB_births_M+ NB_births_F)/C.population as  Birthrate
,      COUNT(NB_deaths_M + NB_deaths_F) as Mortality
from   Demography D 
join   region C
on     (D.Id_city =C.Id_city)
group
by     D.ID_city
,      D.ID_year
,      D.NB_deaths_M
,      D.NB_deaths_F -- <-- added
,      D.NB_births_M
,      D.NB_births_F
,      D.NB_population_AS
,      D.NB_population_ACL
,      C.population -- <-- added