Post reply

Sum Cast to decimal gives wrong totals

  • October 20, 2022 at 6:57 pm

    #4102160

    Hi ,

    I have a weight column that is a Varchar , and I'm doing a select sum on that column and cast it as a decimal. However, I'm getting wrong total doing this, it's off about 40, so its not a rounding issue (Correct total: 1483  Incorrect: 1433)   I manually checked each row and to confirm this.

    Im using....

    SELECT CAST(SUM(OrigWeight /2000) AS decimal(10,2)) Tons 

    SUM(CAST(OrigWeight /2000 AS decimal(10,2))) Tons

    I tried re-arrange the cast after the sum but that didn't help either and got same incorrect total.

    I tried to convert to int , which did work except when it encountered a decimal then crapped out.

    I haven't come across this and any help is appreciated.

    Thank you !

     

     

     

     

  • October 20, 2022 at 7:02 pm

    #4102168

    SELECT CAST(SUM(OrigWeight/2000.0) AS decimal(10,2)) Tons

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • October 20, 2022 at 7:03 pm

    #4102169

    change OrigWeight /2000 to OrigWeight /2000.0

  • October 20, 2022 at 7:51 pm

    #4102175

    Thanks - changed it however Im now getting this error, tried changing the decimal(10,2)    decimal(18,4)    and others

    Msg 8115, Level 16, State 8, Line 54

    Arithmetic overflow error converting nvarchar to data type numeric.

  • October 20, 2022 at 8:16 pm

    #4102189

    Arithmetic overflow error converting nvarchar to data type numeric.

    For what value(s)?

    Can you provide DDL & sample data scripts to reproduce?

    I realize this probably isn't your design, but it's a great example of why numeric (or datetime) data shouldn't be stored as varchar.

  • October 20, 2022 at 8:56 pm

    #4102194

    that means bad data somewhere - so try

    select field..
    from tbl
    where try_convert(decimal(18, 4), field) is null

    to identify the "bad" data

  • October 20, 2022 at 9:00 pm

    #4102196

    I think it is implicitly converting the sum of OriginalWeight to a numeric format that is too small. If you explicitly cast it to an int it seems to work up to 500,000 tons. This assumes the origweight doesn't have decimals of course.

    -- INT SUCCEEDS
    DECLARE @OrigWeight NVARCHAR(100) = '1000000000'
    SELECT CAST(SUM(CAST(@OrigWeight AS INT)/2000.0) AS decimal(10,2)) Tons

    -- NO CONVERSION FAILS
    DECLARE @OrigWeight NVARCHAR(100) = '10000'
    SELECT CAST(SUM(@OrigWeight/2000.0) AS decimal(10,2)) Tons
    GO

    -- DECIMAL(6,2) FAILS
    DECLARE @OrigWeight NVARCHAR(100) = '10000'
    SELECT CAST(SUM(CAST(@OrigWeight AS DECIMAL(6,2))/2000.0) AS decimal(10,2)) Tons
    GO

    -- DECIMAL(7,2) SUCCEEDS FOR 10000
    DECLARE @OrigWeight NVARCHAR(100) = '10000'
    SELECT CAST(SUM(CAST(@OrigWeight AS DECIMAL(7,2))/2000.0) AS decimal(10,2)) Tons
    GO


    -- TRICK INTO USING 7,2 SUCCEEDS FOR 10000
    DECLARE @OrigWeight NVARCHAR(100) = '10000'
    SELECT CAST(SUM(@OrigWeight/20000.0)*10 AS decimal(10,2)) Tons

  • October 20, 2022 at 9:16 pm

    #4102199

    error happens when adding a decimal to this number 2000.0 as per above recommendation to address incorrect sum value. 

    SELECT SUM(CAST(OrigWeight /2000.0 AS decimal(10,2))) Tons
      FROM [dbo].[InboundProcessing]
    Where ProcessCode ='A02- TDU' and MONTH([OrderProcCompleteDate]) = 4 and YEAR([OrderProcCompleteDate]) =2022

    I've attached the sample data, Basically the sum total in the OrigWeight column needs to be 1482 but sums as 1433   (OrigWeight is in pounds so /2000)

    thanks.

     

     

  • October 20, 2022 at 9:19 pm

    #4102200

    I've attached the sample data

    Where? I don't see any input data.

  • October 20, 2022 at 9:22 pm

    #4102201

    Bingo !!  Ed

    That did the trick, I don't think I will ever have a values above 10000 so I will go with this

    SELECT CAST(SUM(@OrigWeight/20000.0)*10 AS decimal(10,2)) Tons

     

     

  • October 20, 2022 at 9:43 pm

    #4102217

    gjoelson 29755 wrote:

    Bingo !!  Ed

    That did the trick, I don't think I will ever have a values above 10000 so I will go with this

    SELECT CAST(SUM(@OrigWeight/20000.0)*10 AS decimal(10,2)) Tons

    But... there's always a future possibility that you will.  It also looks like you might be having SQL Server do a lot more work than necessary.

    What is the original datatype of the OrigWeight column, please?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 20, 2022 at 10:36 pm

    #4102222

    gjoelson 29755 wrote:

    That did the trick, I don't think I will ever have a values above 10000 so I will go with this

    SELECT CAST(SUM(@OrigWeight/20000.0)*10 AS decimal(10,2)) Tons

    I thought this was interesting as it gave us a clue about the implicit conversion of the OrigWeight, however, I don't think I'd use it as production code. I would explicitly cast the column to an int or numeric and not perform the extra multiplication. If it is production code I would at least comment it so that someone doesn't "fix" it in the future.

    SELECT CAST(SUM(CAST(OrigWeight AS INT)/2000.0) AS decimal(10,2)) Tons
    SELECT CAST(SUM(CAST(OrigWeight AS DECIMAL(12,2))/2000.0) AS decimal(10,2)) Tons

  • October 21, 2022 at 1:30 am

    #4102272

    Ed B wrote:

    gjoelson 29755 wrote:

    That did the trick, I don't think I will ever have a values above 10000 so I will go with this

    SELECT CAST(SUM(@OrigWeight/20000.0)*10 AS decimal(10,2)) Tons

    I thought this was interesting as it gave us a clue about the implicit conversion of the OrigWeight, however, I don't think I'd use it as production code. I would explicitly cast the column to an int or numeric and not perform the extra multiplication. If it is production code I would at least comment it so that someone doesn't "fix" it in the future.

    SELECT CAST(SUM(CAST(OrigWeight AS INT)/2000.0) AS decimal(10,2)) Tons
    SELECT CAST(SUM(CAST(OrigWeight AS DECIMAL(12,2))/2000.0) AS decimal(10,2)) Tons

    It is interesting... If the OP would tell us what the DataType for OrigWeight is, it could get a wee bit more interesting... especially since we'd be able to avoid doing the divide for every bloody row.  😀

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 21, 2022 at 3:13 pm

    #4102550

    @gjoelson 29755

    No interest in why I asked the question that you haven't answered, yet?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply