none
Operand data type varchar is invalid for sum operator

    Question

  • Hi i am writting this Case stamt getting this below error , i have used the different cast and convert function here still getting the same error, where my default class having some null values i have put the Isnull,Nullif functions also still not getting the soluction for this


    Select Case When  Command='input' AND Class='default' Then
     Sum('f_input'+'f1_input'+'f2_input'+'f3_input'+'f4_input' )
    When  Command ='Output' AND Class ='default'
    then Sum('f_input'+'f1_input'+'f2_input'+'f3_input'+'f4_input' ) end as BW
     From tblcommand

    • Moved by Olaf HelperMVP Saturday, July 14, 2018 2:56 AM Moved from "Database Design" to a more related forum
    Tuesday, May 29, 2018 9:09 AM

Answers

  • Sum('f_input'+'f1_input'+'f2_input'+'f3_input'+'f4_input' )


    You try to sum string literals, that's not possible, you can only sum up numeric values.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 29, 2018 9:16 AM
  • Hi Ychinnari,

    In your scenario, the error is caused by the Value column is varchar type. You should know that only numeric data type columns can be used in SUM() function.

    So to resolve your issue, you need to convert this Value column from varchar type to numeric data types for example int with CAST() function: CAST(Value as int).

    Since we cannot see your actual data, if there exists non numeric character in Value column. The cast will return error. So use TRY_CAST() instead of CAST(). You can simply verify this with following sample:

    create table #test
    (
    ID int,
    val varchar(10)
    )
    
    insert into #test values
    (1,'10'),
    (2,null),
    (3,'20'),
    (4,'20L')
    
    select SUM(try_cast(val as int))
    from #test 
    
    select SUM(cast(val as int))
    from #test
    
    drop table #test

    Then for your requirement, the query can be like:

    Select SUM(Case When command ='input'AND class in ('default','f_input','f1_input' ,'f2_input','f3_input','f4_input') then TRY_CAST(value as int) end) asBW
    From tblcommand

    Thanks,
    Xi Jin.


    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, May 30, 2018 6:20 AM

All replies

  • Hi,

    Can you explain why do you need to use the sum function?

    If you want to concatenate values, you can use this :

    Select Case When  Command='input' AND Class='default' Then
     'f_input'+'f1_input'+'f2_input'+'f3_input'+'f4_input' 
    When  Command ='Output' AND Class ='default'
    then 'f_input'+'f1_input'+'f2_input'+'f3_input'+'f4_input' end as BW
     From tblcommand

    OR

    Select Case When  Command='input' AND Class='default' Then
     concat('f_input','f1_input','f2_input','f3_input','f4_input') 
    When  Command ='Output' AND Class ='default'
    then concat('f_input','f1_input','f2_input','f3_input','f4_input') end as BW
     From tblcommand


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered"Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]


    Tuesday, May 29, 2018 9:16 AM
  • Sum('f_input'+'f1_input'+'f2_input'+'f3_input'+'f4_input' )


    You try to sum string literals, that's not possible, you can only sum up numeric values.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 29, 2018 9:16 AM
  • what are f_input,f1_input etc?

    Are they columns in your table?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, May 29, 2018 9:30 AM
  • No they are the values in the table,

    Thanks for your quick response.always

    Tuesday, May 29, 2018 9:43 AM
  • this is the query i modified

    Select

    Case  When  command ='input'ANDclass ='default'andclass ='f_input'ANDclass ='f1_input'

    ANDclass ='f2_input'ANDclass ='f3_input'ANDclass ='f4_input'then

    sum(value)endasBW

    Fromtblcommand

    still it throughing the same error


    • Edited by Ychinnari Tuesday, May 29, 2018 12:24 PM
    Tuesday, May 29, 2018 12:23 PM
  • Hi,

    I think your query should be like this :

    Select
    SUM(Case  When  command ='input'AND class in ('default','f_input','f1_input'
    ,'f2_input','f3_input','f4_input') then
    value end) asBW
    From tblcommand

    Note that the value column has to be numeric type.


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, May 29, 2018 12:35 PM
  • here my "Value" is the datatype of varchar it is throughing this error

    Operand data type varchar is invalid for sum operator.

    this is the issue here facing

    Tuesday, May 29, 2018 12:43 PM
  • here my "Value" is the datatype of varchar it is throughing this error

    Operand data type varchar is invalid for sum operator.

    this is the issue here facing

    As Olaf explained before, you can't sum varchar values. you can only sum up numeric values.

    Please, explain what do you want to achieve with you query.


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, May 29, 2018 12:55 PM
  • here my "Value" is the datatype of varchar it is throughing this error

    Operand data type varchar is invalid for sum operator.

    this is the issue here facing

    Try 

    sum(value)

    replacing with

    sum(cast (value as int))
    CAST just tries to convert if the columns has numeric values stored as text. If any one of them cannot be converted then this may too fail. In that case you have work on fixing column type and/or values.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, May 29, 2018 2:14 PM
  • Hi Ychinnari,

    In your scenario, the error is caused by the Value column is varchar type. You should know that only numeric data type columns can be used in SUM() function.

    So to resolve your issue, you need to convert this Value column from varchar type to numeric data types for example int with CAST() function: CAST(Value as int).

    Since we cannot see your actual data, if there exists non numeric character in Value column. The cast will return error. So use TRY_CAST() instead of CAST(). You can simply verify this with following sample:

    create table #test
    (
    ID int,
    val varchar(10)
    )
    
    insert into #test values
    (1,'10'),
    (2,null),
    (3,'20'),
    (4,'20L')
    
    select SUM(try_cast(val as int))
    from #test 
    
    select SUM(cast(val as int))
    from #test
    
    drop table #test

    Then for your requirement, the query can be like:

    Select SUM(Case When command ='input'AND class in ('default','f_input','f1_input' ,'f2_input','f3_input','f4_input') then TRY_CAST(value as int) end) asBW
    From tblcommand

    Thanks,
    Xi Jin.


    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, May 30, 2018 6:20 AM