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