locked
Error message when I use GROUP BY RRS feed

  • Question

  • Hi all,

    I have table with one id int and other columns of varchar(50) .... so I want to use 

    SELECT * FROM Clients GROUP BY cllient_number

    But some error appears ...

    Column 'Client.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


    Wednesday, October 14, 2020 7:59 AM

All replies

  • Next example of that error mentioned above ...

    example 1 - OK

    select client_number from Clients group by client_number

    example 2 - ERROR

    select name,client_number from Clients group by client_number

    Wednesday, October 14, 2020 8:09 AM
  • Hi TakeshiKitano,
    The unaggregated columns in the SELECT of an aggregation query need to match the keys. When you use GROUP BY, you need to list all the result columns that are not aggregates in the GROUP BY clause.
    To know GROUP BY better, you can refer to following links:
    [GROUP BY / aggregate function confusion in SQL]
    [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]]
    [SELECT - GROUP BY- Transact-SQL]
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by TakeshiKitano Wednesday, October 14, 2020 8:44 AM
    • Unmarked as answer by TakeshiKitano Friday, October 16, 2020 10:40 AM
    • Marked as answer by TakeshiKitano Friday, October 16, 2020 10:49 AM
    • Unmarked as answer by TakeshiKitano Tuesday, October 27, 2020 9:34 AM
    Wednesday, October 14, 2020 8:29 AM
  • Thank you,

    I think I understand it better now. So it should looks like ..

    SELECT
    column_1, ... column_n, COUNT(column_x)
    from
    table_name
    group by
    column_1, ... column_n // COUNT(column_x) doesnt need to be after group by


    Wednesday, October 14, 2020 8:37 AM
  • Hi TakeshiKitano,
    I am glad to help you, and you have got a solution. If the response is helpful,we hope you can mark it as an answer. By marking a post as Answered, you help others find the answer faster.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 14, 2020 8:43 AM
  • OK ... 

    and what if I want to group two rows which has same only one some_number ... and other is different? 

    In sqlite it was much easier ... thanks

    Tuesday, October 27, 2020 9:37 AM
  • Hi TakeshiKitano,
    You can refer to this thread that using group by on multiple columns.
    And if the response is helpful, please mark it as an answer.
    Thank you for your understanding.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 27, 2020 9:48 AM