Division of integers returns 0
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 Percentfrom testgroup by random_intorder by random_int;
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.