none
Operand data type varchar is invalid for sum operator. Error

    Question

  • select
    C.cust_id_pkey,
    sum(F.fac_limitamount)As fac_limitamount,
    sum(F.fac_balance) As fac_balance 
    from
    dbo.tbl_element_facilities F
    JOIN dbo.tblCCRS_Relations R ON F.fac_id_pkey=R.FacilityID
    JOIN tbl_element_customers C ON R.CustomerID=C.cust_id_pkey
    Group By C.cust_id_pkey--,F.fac_limitamount,F.fac_balance

    While runnin gthis query I Mgetting following error.

    Msg8117,Level16,State1,Line2

    Operand data type varchar is invalid for sum operator.

    Friday, May 18, 2012 10:50 AM

Answers

  • try this.

    select 
    C.cust_id_pkey,
    sum(CAST(replace(F.fac_limitamount,'(USD)','') AS INT))As fac_limitamount,
    sum(CAST(replace(F.fac_balance,'(USD)','')AS INT)) As fac_balance  
    from 
    dbo.tbl_element_facilities F 
    JOIN dbo.tblCCRS_Relations R ON F.fac_id_pkey=R.FacilityID 
    JOIN tbl_element_customers C ON R.CustomerID=C.cust_id_pkey
    Group By C.cust_id_pkey,F.fac_limitamount,F.fac_balance,F.fac_id_pkey
    
     

    if still conversion error exists. you need to find out all invalid int values and replace them.


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Friday, May 18, 2012 12:05 PM
  • Should we be surprised?

    You need to talk with the owner of the data, how this data is intended to be used. You can run the query with Replace as suggested by Shamas, but I would not be surprised if you get a new error message.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 18, 2012 1:08 PM

All replies

  • Hi, what are the data types for fac_limitamount and fac_balance? If they're stored as varchar but they have numbers try using cast:

    sum(cast(F.fac_limitamount as float)) as fac_limitamount

    David.

    Friday, May 18, 2012 10:57 AM
  • Hi,

    Either F.fac_limitamount or F.fac_balance will be of data type varchar, which cannot be used with SUM.

    You'll either have to cast them to ints if the data will allow it:

    select 
    C.cust_id_pkey,
    sum(CAST(F.fac_limitamount AS INT))As fac_limitamount,
    sum(CAST(F.fac_balance AS INT)) As fac_balance  
    from 
    dbo.tbl_element_facilities F 
    JOIN dbo.tblCCRS_Relations R ON F.fac_id_pkey=R.FacilityID 
    JOIN tbl_element_customers C ON R.CustomerID=C.cust_id_pkey
    Group By C.cust_id_pkey--,F.fac_limitamount,F.fac_balance
    Or change the column to the correct data type.


    Zach Stagers - scratchbox.co.uk
    MCITP: Database Developer 2008
    Remember to Mark as Answer and Vote as Helpful


    Friday, May 18, 2012 10:57 AM
  • pls try

    select
    C.cust_id_pkey,
    sum(case when ISNUMERIC(F.fac_limitamount) then  cast(F.fac_limitamount as int) else 0 end)As fac_limitamount,
    sum(case when ISNUMERIC(F.fac_balance) then  cast(F.fac_balance as int) else 0 end) As fac_balance 
    from
    dbo.tbl_element_facilities F
    JOIN dbo.tblCCRS_Relations R ON F.fac_id_pkey=R.FacilityID
    JOIN tbl_element_customers C ON R.CustomerID=C.cust_id_pkey
    Group By C.cust_id_pkey--,F.fac_limitamount,F.fac_balance

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Hasham NiazEditor Friday, May 18, 2012 11:05 AM
    • Unproposed as answer by SR_MCTS Friday, May 18, 2012 11:56 AM
    • Proposed as answer by Naomi NModerator Friday, May 18, 2012 1:11 PM
    Friday, May 18, 2012 10:59 AM
  • In addition to the other posts, talk with the person who is responsible for the schema and have the data type changed to int or some other numeric data type. Have money values in varchar columns will bring you a lot of misery.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 18, 2012 11:14 AM
  • select
    C
    .cust_id_pkey,
    sum
    (CAST(F.fac_limitamount AS INT))As fac_limitamount,
    sum
    (CAST(F.fac_balance AS INT)) As fac_balance 
    from
    dbo
    .tbl_element_facilities F
    JOIN dbo.tblCCRS_Relations R ON F.fac_id_pkey=R.FacilityID
    JOIN tbl_element_customers C ON R.CustomerID=C.cust_id_pkey
    Group By C.cust_id_pkey,F.fac_limitamount,F.fac_balance,F.fac_id_pkey

    Order by 1 Asc

    After runining the above query i am getting the following error....

    Msg245,level16,state 1 line 2

    conversion failed when converting the varchar value'0(USD)' to data type int.

    Friday, May 18, 2012 11:47 AM
  • try this.

    select 
    C.cust_id_pkey,
    sum(CAST(replace(F.fac_limitamount,'(USD)','') AS INT))As fac_limitamount,
    sum(CAST(replace(F.fac_balance,'(USD)','')AS INT)) As fac_balance  
    from 
    dbo.tbl_element_facilities F 
    JOIN dbo.tblCCRS_Relations R ON F.fac_id_pkey=R.FacilityID 
    JOIN tbl_element_customers C ON R.CustomerID=C.cust_id_pkey
    Group By C.cust_id_pkey,F.fac_limitamount,F.fac_balance,F.fac_id_pkey
    
     

    if still conversion error exists. you need to find out all invalid int values and replace them.


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Friday, May 18, 2012 12:05 PM
  • Should we be surprised?

    You need to talk with the owner of the data, how this data is intended to be used. You can run the query with Replace as suggested by Shamas, but I would not be surprised if you get a new error message.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 18, 2012 1:08 PM