ratio of rows that have certain value in a given column to the total number of rows in SQL

Question

Assume I have a table as follow:

product feat1 value 
A       1    2
A       2    0
A       3    3
A       4    0
B       1    1
B       2    0
C       1    1
C       2    2
C       3    4

I am trying to find the ratio of zero in each product, so for example the output would be:

product ratio
  A      0.5
  B      0.5
  C      0 

For whatever reason I cannot make this to work, here is what I have done:

select product, count(*) from Table where value=0 group by product 

This gives me the number of rows where there is a zero in product, however this does not include C as it does not have zero to begin with. I also do not know how to include the ratio into place. I was thinking to do it with case statement but still could not make it to work with group by. I was thinking to do it with common table expression for the ratio, but I think I am over complicating the problem.

Answer 1

Use AVG() aggregate function over the boolean expression value = 0:

SELECT product,
       AVG(value = 0) AS ratio
FROM tablename
GROUP BY product;

See the demo.