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.
Any insight as to what I am doing wrong?
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;