Arithmetic overflow or other arithmetic exception occurred solution?

Question

I'm using db2, not sure what version. I'm getting an overflow error when trying to sum a table. I thought that I would be able to cast the sum to a BIGINT which seems to work for a sum total but I'm looking to get a percentage and when I cast to a BIGINT my data is inaccurate. How do I get an accurate percentage for Percent_DeliveredB/A? Converting the numerator and denominator to BIGINT and dividing for percentage is not giving me the correct results.

Here's my script:

SELECT 
FAT.DIM_BUILDING_ID,
FAT.BUILDING_NAME,
SUM(CAST(FAT.AMOUNT AS BIGINT)) AS SALES_SUM,
SUM(CAST(FAT.ORDERS AS BIGINT)) AS ORDERS_SUM,
SUM(CAST(FAT.CAPABILITY AS BIGINT)) AS CAPABILITY_SUM,
SUM(FAT.ORDERS_B)/sum(FAT.Amount) AS Percent_DeliveredB,
SUM(FAT.ORDERS_A)/sum(FAT.Amount) AS Percent_DeliveredA,
SUM(CAST(FTS.GROUP_A AS BIGINT)) AS GROUP_A,
SUM(CAST(FTS.GROUP_B AS BIGINT)) AS GROUP_B,
SUM(CAST(FTS.GROUP_C AS BIGINT)) AS GROUP_C  

FROM ORDERS AS FAT
INNER JOIN GROUPS AS FTS ON FAT.DIM_PROJECT_ID = FTS.DIM_PROJECT_ID
GROUP BY FAT.DIM_BUILDING_ID, FAT.BUILDING_NAME;

I tried the following but it comes back with 0 for the percentage.

SUM(CAST(FAT.ORDERS_B AS BIGINT))/sum(CAST(FAT.Amount AS BIGINT)) AS Percent_DeliveredB

What is your question?

mustaccio
– mustaccio

2013-09-25 14:39:06 +00:00
Commented Sep 25, 2013 at 14:39 — mustaccio
– mustaccio, Commented Sep 25, 2013 at 14:39
I edited my question. Hopefully it's more clear.

Tone
– Tone

2013-09-25 14:50:41 +00:00
Commented Sep 25, 2013 at 14:50 — Tone
– Tone, Commented Sep 25, 2013 at 14:50

Tone · Accepted Answer · 2013-09-25 16:10:56Z

4

I was able to get the correct results converting to double.

SUM(CAST(FAT.ORDERS_B AS DOUBLE))/sum(CAST(FAT.Amount AS DOUBLE)) AS Percent_DeliveredB,

answered Sep 25, 2013 at 16:10

Tone

78310 gold badges25 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mustaccio · Accepted Answer · 2013-09-25 15:03:00Z

2

It is giving you a correct result, as any value less than 1, when cast to an integer (or BIGINT for that matter) will be truncated to 0, obviously. If you are expecting a fractional number, use DECIMAL or FLOAT data types:

cast(SUM(FAT.ORDERS_B) as decimal(10,2)) / 
cast(sum(FAT.Amount) as decimal(10,2)) AS Percent_DeliveredB

Use the correct precision for your needs, of course.

answered Sep 25, 2013 at 15:03

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Comments

J. Doe · Accepted Answer · 2018-08-30 12:50:13Z

2

I got the same error while using a select count(*) on a very big table.

I solved it by using count_big(*) which can handle a count that is more than 2.147.483.647 (the MAX_LONGINT_INT SQL limit).

answered Aug 30, 2018 at 12:50

J. Doe

13.5k9 gold badges79 silver badges146 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow or other arithmetic exception occurred solution?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related