Menu
I have a table with a smallint column that contains percentages as whole numbers (i.e., 50, 75, 85, etc.)
When I divide this column by 100, as in
SELECT MY_COLUMN/100 AS PCT_AS_FRACTION FROM MY_TABLE the result is rounded to the nearest whole number. For example, for a row that contains the number "50", I get zero as my result.
I can duplicate this with a simple statement:
SELECT 50 / 100 AS TEST_VALUE Why is that, and how can I get more precision in my result?
448
You have to specify the number of places to the right of the decimal, like this: decimal(10,2) - ten digits, with two to the right of the decimal point.
Floor division simplified means that the real number result of the division is always rounded down. For example, 7 divided by 3 would give 1.75 . And using floor division, the result would be 1 because no matter what comes after the decimal point, we would always round down.
Integer division in SQL takes place when both the dividend and the divisor are integers. Since they are integers, SQL wants to return an integer result to match the number type. In PostgreSQL and SQL Server, it is integer division. In MySQL and Oracle it is regular division (and the result of 1/4 is 0.25 ).
Answers. SELECT ( Cast (Col1 AS Float) / Cast (Col2 AS Float) ) AS [Value] FROM ..... SELECT CAST(Col1 as decimal(10,2)) / CAST(col2 as decimal(10,2)) as [Value] FROM ... SELECT ( Cast (Col1 AS Float) / Cast (Col2 AS Float) ) AS [Value] FROM .....
When you do integer division (integer divided by integer) you always get an integer answer. 50/100 = .50, which is 0 in integer-speak.
Have you tried dividing MY_COLUMN by 100.0?
86
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
