26

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:

enter image description here

I tried : 

select cast(1701 / 84936 as numeric (10,10));

but the result was 0.0000000000.

How could I solve this silly thing?

Rodrigue
  • 3,617
  • 2
  • 37
  • 49
Andres Urrego Angel
  • 1,842
  • 7
  • 29
  • 55

2 Answers2

38

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 2
    If you remove the reference to `FLOAT` then I'll upvote ;) Anyone that asks this question doesn't need directing to the den of vipers that is floating point arithmetic... – MatBailie May 23 '18 at 17:53
  • 1
    Dude thanks so much I came from SQL school then bunch of things are a bit different here. I knew it it was something silly. – Andres Urrego Angel May 23 '18 at 17:53
  • @MatBailie Yes, you are right that float could be dangerous. For instance: https://stackoverflow.com/questions/33322778/avg-of-float-inconsistency – Lukasz Szozda May 23 '18 at 17:58
3

When mixing data types the order counts

Note that the order of the elements in a math expression counts for the data type of the result.
Let's assume that we intend to calculate the percentage unit_sales/total_sales where both columns (or numbers) are integers.

See and try with this code here.

-- Some dummy table
drop table if exists sales;
create table sales as 
    select 3 as unit_sales, 9 as total_sales;

-- The calculations
select
    unit_sales/total_sales*100,   --> 0 (integer)
    unit_sales/total_sales*100.0, --> 0.0 (float)
    100.0*unit_sales/total_sales  --> 33.3 (float and expected result)
from sales;

The output

  0 | 0.0 | 33.33
  1. The first column is 0 (integer) because of 3/9=0 in an integer division.
  2. The second column is 0.0 because SQL first got the integer 0 (3/9), and later, SQL converts it to float in order to perform the multiplication by 100.0.
  3. The expected result.
    The non-integer 100.0 at the beginning of the expression force a non-integer calculation.
ePi272314
  • 12,557
  • 5
  • 50
  • 36