sql with rounding issue
I have a problem with sql(maths).
I have a total payable given to vendor which is 33.333, and I need to divide the amount with two users. So I have select
select (16.666 * 2) from dual which gives me 33.332 that is .1 less than the total amount I have to give.
If I have this sql select (16.667 * 2) from dual, then it gives me 33.334 which .1 greater than 33.333.
How can I divide the total amount which I could equally distribute?
I'm not sure from where are you executing your query, but it works here (SQLDeveloper, 10g):
SELECT (33.333 / 2) FROM dual; 16,6665 SELECT (16.6665 * 2) FROM dual; 33,333
Do it the other way around:
You are most likely working with the wrong column type. You should be using DECIMAL instead of e.g. FLOAT.
Here is a good summary: http://lists.mysql.com/mysql/189592
Depending on the SQL standard you are using the type can be MONEY, DECIMAL or NUMBER.