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

  • Simple way to transpose columns and rows in Sql?
  • Foreign key with additional relationship constraint
  • SQL Server connection pool doesn't detect closed connections?
  • The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption
  • java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/db
  • what does the access 2007 runtime enable me to do?
  • 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.

  • Unable to connect to SQL database - C#, VS2012, SQL Server 2012
  • Find a specific substring using Transact-SQL
  • SQL Job having issues with transaction log
  • Script to save varbinary data to disk
  • Summarize aggregated data
  • TSQL to return NO or YES instead TRUE or FALSE
  • 3 Solutions collect form web for “Group by is throwing error in SQL Server”

    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
    

    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.

    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/

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.