Sum Cast to decimal gives wrong totals

Question

Sum Cast to decimal gives wrong totals

gjoelson 29755

SSC Eights!

Points: 848
More actions
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 !

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

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

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

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

frederico_fonseca SSCoach Points: 16155 More actions · Answer 2

frederico_fonseca

SSCoach

Points: 16155

October 20, 2022 at 7:03 pm

#4102169

change OrigWeight /2000 to OrigWeight /2000.0

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 3

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.

ratbak Ten Centuries Points: 1358 More actions · Answer 4

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.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 5

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

Ed B Hall of Fame Points: 3666 More actions · Answer 6

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

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 7

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.

ratbak Ten Centuries Points: 1358 More actions · Answer 8

I've attached the sample data

Where? I don't see any input data.

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

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)

Ed B Hall of Fame Points: 3666 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

@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)