Percentage calculation of specific number of row over total

Question

I'm looking for SELECT statement to calculate percentage of specific number of row over the total number of rows. For example; lets say i have a FRUIT table like this;

I want to calculate a percentage of rows that its name is not peach, over the total number of rows. I try this statement :

SELECT CAST((select count(name) from fruit WHERE name !='peach') 
as FLOAT) /
            (select count(name)from fruit)*100.0 as percentage ;

but it doesn't give me correct number. I also need a statement that i can calculate percentage of each fruit by grouping them with Group by function

I'm very new at SQL and i keep trying but cant find the right syntax. Please help me.

Answer 1

I think the easiest way to do this is using conditional aggregation with average:

select avg(case when fruit <> 'peach' then 100.0 else 0.0 end)
from fruits;

In Postgres, you can use the shorthand:

select 100*avg((fruit <> 'peach')::int)
from fruits;