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

  • SQL Server 2005 ROW_NUMBER() without ORDER BY
  • SQL Function returning sum value of all rows
  • Migration from Cognos cubes to SSAS
  • Find available openings using SQL
  • Shrinking log file in SQL Server has no change in file size
  • Convert MonthName to month Number SSRS
  • 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.

  • SQL Server SQL Statement - Updating record
  • Fetch return value from a stored procedure
  • Parse a date from unformatted text in SQL
  • Fire and forget pattern using BeginNonExecuteQuery()
  • Wrong sql query generated by hibernate
  • SQL Query: Each GROUP BY expression must contain at least one column that is not an outer reference
  • 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.