CODEWITHSUNDEEP
mysqlsqlsql-servert-sql
KateMak
KateMak

Reputation: 1649

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 this 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

Upvotes: 0

Views: 242

Answers (3)

MacWise
MacWise

Reputation: 530

you have to include all the columns in the group by that are not in an 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

Upvotes: 1

gunjan maheshwari
gunjan maheshwari

Reputation: 306

You can only have those columns in select clause which you have included in group by clause. Any other column can only be selected if you apply aggregate function on them. For Ex- (correct one)

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

(wrong one)

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

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 3

Related Questions