You must Sign In to post a response.
  • Category: SQL Server

    Error showing in case when

    Hi i want to display id from a table having a condition that if id is null then id=300 else max(id)+1 as id from table .

    i tried this with case when statement as follows

    select case when (conductor_id is null)then 300 else MAX(conductor_id)+1 end as conductor_id from conductormaster
    i got the error as

    Column 'conductormaster.conductor_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    how to solve this

    Regards

    Baiju
  • #747268
    Max is an aggregate function.You need to add a GROUP BY for "conductor_id" columns at the end.

    select case when (conductor_id is null)then 300 else MAX(conductor_id)+1 end as conductor_id from conductormaster GROUP BY conductor_id

  • #747269
    When you use aggregate functions in select, the selected column has to be used in group by clause.

    select case when (conductor_id is null) then 300 else MAX(conductor_id)+1 end as conductor_id from conductormaster group by conductor_id

    This has to work...

    SanAroJesu
    sanarojesu@yahoo.com
    9620870608

  • #747358
    Hi,

    As per error description you are using Aggregate functions like (SUM, MIN, MAX...) when you use those aggregate functions then you must call the rest of selected columns in grouping clause. If you are not called those columns in Grouping clause then obviously its throwing that error.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


  • Sign In to post your comments