Allocation of integers 0

Question

I feel like I'm missing something obvious. I am trying to check 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 Percentage column returns zero for each record. I tried to use it as a float, as a decimal, I tried to change the random_int column to decimal, and not to integer, always to the same result.

Here is the fiddle.

Any insight on what I'm doing wrong?

Answer 1

You must distinguish before sharing, but also you do not have enough subquery to get the total from the table. Here is an example .

 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; 

Answer 2

Try this request:

 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 type system. In your case, the type is implied by the count() function, which returns the column bigint (or int8 ) and id , which is equal to integer .

I would recommend using 100.0 as the starting factor, which will cause the whole expression to be calculated as a number, and also provide real percentages. You can also click on numeric(5,2) at the end to get rid of too many numbers.