的Sql AVG包括在内，但因为它不是在聚合函数或GROUP BY子句

Question

包含越来越列在选择列表中无效我，为什么我收到此错误很困惑：

"Column 'City.CityID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

我有两个表格：一个叫城市与城市信息，如id，名称等另一个表包含cityid和评级（可以有许多行，具有相同cityid和评级）。

我有一个group作者为城市，我只是在RateCity表中的平均评级。那么为什么这会失败呢？

select City.CityID, City.CityName, City.CityStateOrProvince, 
City.CityCountry, City.CityImageUri, AVG(RateCity.Rating) as AverageRating 
from City 
right join RateCity 
on City.CityID = RateCity.CityID 
group by City.CityID, RateCity.CityID 

Answer 1

您需要按所有非聚合进行分组。

select City.CityID 
     ,City.CityName 
     ,City.CityStateOrProvince 
     ,City.CityCountry 
     ,City.CityImageUri 
     ,AVG(RateCity.Rating) as AverageRating 
from City 
right join RateCity on City.CityID = RateCity.CityID 
Group by 
     City.CityID 
     ,City.CityName 
     ,City.CityStateOrProvince 
     ,City.CityCountry 
     ,City.CityImageUri 

另一种选择是

select City.CityID 
     ,City.CityName 
     ,CityStateOrProvince = max(City.CityStateOrProvince) 
     ,CityCountry   = max(City.CityCountry) 
     ,CityImageUri  = max(City.CityImageUri) 
     ,AverageRating  = AVG(RateCity.Rating) 
from City 
right join RateCity on City.CityID = RateCity.CityID 
Group by 
     City.CityID 
     ,City.CityName 

Answer 2

你只能有您已被列入条款组中的SELECT子句中的列。只有在您对其应用聚合函数时才能选择其他列。 为前（正确的）

SELECT a,b,sum(c) 
From d 
GROUP BY a,b; 

（错误的）

SELECT a,b,sum(c) 
FROM d 
GROUP BY a; 

Answer 3

你必须包括该组中的所有列由是不是在aggreate

SELECT City.CityID, 
     City.CityName, 
     City.CityStateOrProvince, 
     City.CityCountry, 
     City.CityImageUri, 
     AVG(RateCity.Rating) as AverageRating 
FROM City 
RIGHT JOIN RateCity on City.CityID = RateCity.CityID 
GROUP BY City.CityID, City.CityName, City.CityStateOrProvince, 
City.CityCountry, City.CityImageUri