CODEWITHSUNDEEP
c#sqlsql-serverdatabase
Pankaj Sharma
Pankaj Sharma

Reputation: 236

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.

Upvotes: 1

Views: 1700

Answers (3)

Vecchiasignora
Vecchiasignora

Reputation: 1315

you should grouping every column which not in aggregate function, or use aggregate function for those columns

first like this

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, a.ser_id,b.UserID,b.Latitude,b.Longitude

or second like this

    select  
    max(c.service_id) as service_id, 
    max(a.ser_id), max(b.UserID), 
    sum(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

Upvotes: 1

Satish Kumar
Satish Kumar

Reputation: 110

If you want use group by then all the columns selected should be in aggregate functions and if you do not want to apply any aggregate function on those columns then they must be in group by clause. In your case you should try :

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,**a.ser_id,b.UserID**

Or you can just remove a.ser_id,b.UserID if you don't want this

Or if you want these columns then you must apply group by or any aggregate function on a.ser_id,b.UserID else they will not work as your server looks in strict mode. So if you want to run you query as it is then you have to disable strict mode on your data base . If you are using MYSQl then this link might help you https://www.liquidweb.com/kb/how-to-disable-mysql-strict-mode/

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37337

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.

Upvotes: 1

Related Questions