NickName:Pankaj Sharma Ask DateTime:2017-05-18T13:29:49

Group by is throwing error in SQL Server

I want to group by service name to all my record and I am using this query in SQL Server but it's throwing an error

select max(c.service_id) as service_id, a.ser_id, b.UserID, 
    SQRT(POWER(69.1 * ( @latitude - b.Latitude),2) + POWER(69.1 * ( b.Longitude - @longitude ) * COS(b.Longitude / 57.3), 2)) as distance,
    c.service_name
from aspnet_bawe_services a 
left join aspnet_user_account b on a.bawe_id = b.UserID 
left join aspnet_services_app c on a.ser_id = c.service_id 
group by c.service_name

Error

Msg 8120, Level 16, State 1, Procedure app_service_list, Line 24
Column 'aspnet_bawe_services.ser_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Copyright Notice：Content Author:「Pankaj Sharma」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/44039300/group-by-is-throwing-error-in-sql-server

Answers

Vecchiasignora 2017-05-18T05:36:54

you should grouping every column which not in aggregate function, or use aggregate function for those columns\n\nfirst like this\n\nselect max(c.service_id) as service_id, a.ser_id ,b.UserID,SQRT(POWER(69.1 * ( @latitude - b.Latitude),2) + POWER(69.1 * ( b.Longitude - @longitude ) * COS(b.Longitude / 57.3), 2)) as distance,c.service_name\n from aspnet_bawe_services a \n left join aspnet_user_account b on a.bawe_id=b.UserID \n left join aspnet_services_app c on a.ser_id=c.service_id \n group by c.service_name, a.ser_id,b.UserID,b.Latitude,b.Longitude\n\n\nor second like this\n\n select \n max(c.service_id) as service_id, \n max(a.ser_id), max(b.UserID), \n sum(SQRT(POWER(69.1 * ( @latitude - b.Latitude),2) + POWER(69.1 * ( b.Longitude - @longitude ) * COS(b.Longitude / 57.3), 2))) as distance,\n c.service_name\nfrom \n aspnet_bawe_services a \nleft join \n aspnet_user_account b on a.bawe_id = b.UserID \nleft join \n aspnet_services_app c on a.ser_id = c.service_id \ngroup by \n c.service_name\n",

Michał Turczyn 2017-05-18T05:34:20

When you group by, it means that you want to ,,compress\" multiple rows, which coressponds to one value in column, which you want to group by. Since ALL columns (exept the one you are grouping by) must be contained in an aggregating function (like max or sum, etc.), even if it's not necessary! Because SQL doesn't know what you might know :) So, you have to put a.ser_id in some aggregate function.",