is invalid in the select list because it is not contained in either an aggregate func

[fran7]

Hi, Same old problem I see everywhere but dont quite get the solution.

I have this query.

keywords_cmd.CommandText = "SELECT city,galleryimage1,galleryimage2,galleryimage3,galleryimage4,galleryimage5,galleryimage6 FROM databasetable where city like '%"  & Request.QueryString("term") & "%' and galleryimage1<>'' and galleryimage2<>'' and galleryimage3<>'' and galleryimage4<>'' and galleryimage5<>'' and galleryimage6<>'' group by city"

I get the error "is invalid in the select list because it is not contained in either an aggregate func"

I simply want to select city, and group by city to stop duplicates appearing in my list but also need the where condition to control which ones appear.


Any pointers would be great
Thanks in advance.
Richard

[Luk3r]

You must GROUP BY all columns that you are selecting.

SELECT city, galleryimage1, galleryimage2, galleryimage3, galleryimage4, galleryimage5, galleryimage6
FROM databasetable
WHERE city like '%"  & Request.QueryString("term") & "%' and galleryimage1<>'' and galleryimage2<>'' and galleryimage3<>'' and galleryimage4<>'' and galleryimage5<>'' and galleryimage6<>'' 
GROUP by city, galleryimage1, galleryimage2, galleryimage3, galleryimage4, galleryimage5, galleryimage6

Or, for you:

keywords_cmd.CommandText = "SELECT city, galleryimage1,galleryimage2,galleryimage3,galleryimage4,galleryimage5,galleryimage6 FROM databasetable WHERE city like '%"  & Request.QueryString("term") & "%' and galleryimage1<>'' and galleryimage2<>'' and galleryimage3<>'' and galleryimage4<>'' and galleryimage5<>'' and galleryimage6<>'' GROUP by city,galleryimage1,galleryimage2,galleryimage3,galleryimage4,galleryimage5,galleryimage6"

[Rabbit]

Since you're not aggregating anything, why not just use distinct?