Menu
I'm trying to do something really quite basic to calculate a kind of percentage between two columns in Redshift. However, when I run the query with an example the result is simply zero because the decimals are not being covered.
code:
select 1701 / 84936;
Output:
I tried :
select cast(1701 / 84936 as numeric (10,10));
but the result was 0.0000000000.
How could I solve this silly thing?
925
The CEILING or CEIL function is used to round a number up to the next whole number. (The FLOOR function rounds a number down to the next whole number.)
Returns true for numbers of scale 0 in the 32-bit range, and false for anything else (including null and floating point numbers). The is_integer function is a superset of the is_smallint function.
When numeric values are computed during query processing, you might encounter cases where the computation is impossible and the query returns a numeric overflow error. You might also encounter cases where the scale of computed values varies or is unexpected.
It is integer division. Make sure that at least one argument is: NUMERIC(accurate data type)/FLOAT(caution: it's approximate data type):
/ division (integer division truncates the result)
select 1701.0 / 84936;
-- or
SELECT 1.0 * 1701 / 84936;
-- or
SELECT CAST(1701 AS NUMERIC(10,4))/84936;
DBFiddle Demo
66
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
