KateMak KateMak - 2 months ago 6
MySQL Question

Sql AVG included but getting Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I am very confused as to why I am getting the error "Column 'City.CityID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I have two tables: one called City with city info, such as id, name, etc and another table containing the cityid and rating (there can be many rows with the same cityid and rating).

I have a group by for the city, and I am simply taking the average of the rating in the RateCity table. So why would this be failing?

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

You need to group by all non-aggregates.

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

Another option is

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