Question 58191 in Mysql Question posted in
The official MySQL documentation can be found here.

Find ratio of rows that have zero in a given column to the total number of rows in SQL – Mysql

Assume I have a table as follow:

product date 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.

Tags:

2

Answers


  1. 0

    This can be done using group by and sum(value=0)/count(1)

    select product, sum(value=0)/count(1) as ratio
from mytable
group by product

    Demo here

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search