I'm trying to find the percentage of rows that meet a specific condition. My query is close but the answer is always rounded to the nearest whole number.
For instance this query below is returning 6 but it should return 6.25. Meaning 6.25% of the rows meet that condition. How would I do this?
select sum(case when name like 'H%' then 1 else 0 end) * 100 / count(*)
from category
Just add a decimal point:
select sum(case when name like 'H%' then 1.0 else 0 end) * 100 / count(*)
from category;
Postgres does integer division.
You can also express this using avg():
select avg(case when name like 'H%' then 100.0 else 0 end)
from category;
The decimal point is not needed. Although Postgres does integer division of integers, it calculates average of integers using decimal points.
And this can be phrase more simply (assuming that name is not NULL):
select avg( (name like 'H%')::int ) * 100
from category;
You need a decimal value, otherwise, since both the numerator and denominator are integers, Postgres does integer division.
But here, I would just use avg() - for which you don't need an integer value:
select avg(case when name like 'H%' then 1 else 0 end) * 100
from category
You're performing integer division, and thus omitting everything right of the decimal point. If you use a floating-point literal 100.0, you should be OK:
select sum(case when name like 'H%' then 1 else 0 end) * 100.0 / count(*)
from category