Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2013
    Posts
    14

    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:

    Code:
    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.

    Code:
    SUM(CAST(FAT.ORDERS_B AS BIGINT))/sum(CAST(FAT.Amount AS BIGINT)) AS Percent_Delivered

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try casting the percent calculations to DOUBLE. In INTEGER division, dividing by a larger number gets you 0 (98/100 = 0 in INTEGER).

    Andy

  3. #3
    Join Date
    Aug 2013
    Posts
    14
    Perfect. Thanks Andy.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •