Division of integers returns 0

Question

I feel like I'm missing something obvious. I am trying to test out the distribution of random(). Here is the table:

create table test (
  id int,
  random_float float,
  random_int int
);

Here is what I want to do:

truncate table test;
insert into test (id)
values (generate_series(1,1000));
update test 
set
  random_float = random() * 10 + 1;
update test
set
  random_int = trunc(random_float);
select 
  random_int,
  count(random_int) as Count,
  cast( count(random_int) / max(id) as float) as Percent
from test
group by random_int
order by random_int;

However, the "Percent" column returns zero for every record. I tried casting it as float, as decimal, I tried changing the random_int column to decimal instead of integer, always same result.

Here is a fiddle.

Any insight as to what I am doing wrong?

Cast to `numeric` (or `float`, though I prefer the former) *before* you divide. — vyegorov, Oct 23 '14 at 20:48
You can reproduce this in one line: `select 1/2`. Returns 0. — usr, Oct 23 '14 at 20:49
@Phrancis, you should cast *before* you divide: `select 1/2, 1.0/2, 1.0*1/2, 1::numeric/2, cast(1 as float)/2;` — vyegorov, Oct 23 '14 at 21:02

score 30 · Accepted Answer · answered Oct 23 '14 at 21:05

30

You should cast before you divide, but also you were missing a subquery to get the total count from the table. Here's the sample.

select 
  random_int,
  count(random_int) as Count,
  cast(count(random_int) as decimal(7,2)) / cast((select count(random_int) from test) as decimal(7,2)) as Percent
from test
group by random_int
order by random_int;

answered Oct 23 '14 at 21:05

Jaaz Cole

3,119
1
15
20

1

I knew it had to be to do with casting, so thanks 6 years later still a helpful answer! In my case I was casting the entire calc as decimal(10,2) which yielded 0 but casting the individual components worked a treat. Still learning! – Hilary Feb 18 '21 at 09:49
1

It is enough to cast one of the numbers (e.g. numerator), then the whole expression becomes decimal. – Milan Nov 29 '21 at 02:33

score 4 · Answer 2 · answered Oct 23 '14 at 21:10

Try this query instead:

select 
       random_int,
       count(random_int) as Count,
       cast( count(random_int) / max(id) as float) as Percent,
       (100.0 * count(random_int) / max(id))::numeric(5,2) as pct
  from test
 group by random_int
 order by random_int;

PostgreSQL has a strong types system. In your case, type is implied by count() function, which returns bigint (or int8) and id column, which is integer.

I would recommend using 100.0 as initial multiplier, that'll cause whole expression to be calculated as numeric and will also provide real percents. You might also want to cast to numeric(5,2) at the end to get rid of too big number.

Division of integers returns 0

2 Answers2

Linked

Related