sql - 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?

Answer 1

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;