Unanswered: Arithmetic overflow or other arithmetic exception occurred solution?
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.
Here's my script:
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_Delivered