Skip to Main Content

Prevent percent Calculation from going over 100 %

mlov83Dec 14 2022 — edited Dec 14 2022

Below you will find my table DDL, inserts and query. I'm simply trying to calculate a percentage based on my_budget over total_budget. For all intents and purpose total budget is a static amount so it will not differentiate from row to row.The issue however is that in certain situations it appears that the total percent calculation is greater than 100%. If you look at my results I end up with 100.01. Can someone give me a pointer how to prevent that from happening. I feel like I'm missing something simple on this one. I really don't care where the -.01 gets reduced from my percent calculation to get an even 100%.

-------------------------------------------------------
--  DDL for Table PLAY_TABLE
--------------------------------------------------------

  CREATE TABLE PLAY_TABLE 
   (MY_BUDGET NUMBER(11,2), 
    VCOAS VARCHAR2(1 CHAR), 
    VFUND VARCHAR2(6 CHAR), 
    VORGN VARCHAR2(6 CHAR), 
    VACCT VARCHAR2(6 CHAR), 
    TOTAL_BUDGET NUMBER(11,2)
   ) 
REM INSERTING into PLAY_TABLE
SET DEFINE OFF;
Insert into PLAY_TABLE (MY_BUDGET,VCOAS,VFUND,VORGN,VACCT,TOTAL_BUDGET) values (535.5,'D','110001','3013','2041',101745);
Insert into PLAY_TABLE (MY_BUDGET,VCOAS,VFUND,VORGN,VACCT,TOTAL_BUDGET) values (4819.5,'D','110001','3304','2041',101745);
Insert into PLAY_TABLE (MY_BUDGET,VCOAS,VFUND,VORGN,VACCT,TOTAL_BUDGET) values (96390,'D','110001','3304','2211',101745);


SELECT round((my_budget / total_budget) * 100,  2) calculated_budget,
       total_budget,
       vcoas,
       vfund,
       vorgn,
       vacct,
       my_budget
  FROM play_table

 --results--
0.53    D   110001  3013    2041    535.5   101745
4.74    D   110001  3304    2041    4819.5  101745
94.74   D   110001  3304    2211    96390   101745
This post has been answered by Solomon Yakobson on Dec 15 2022
Jump to Answer
Comments
Post Details
Added on Dec 14 2022
10 comments
201 views