I like to get the amount of rows where a certain field is either 1 or 0.
My table looks like this:
ID | name | my_bool
===================
1 | foo | 1
2 | bar | 1
3 | loo | 0
4 | zoo | 1
as result I expect
YES | NO | percentage
======================
3 | 1 | 0.3333
YES
is how many rows where my_bool
is true (1) while NO
is where rows with false (0)
percentage
give the percentage of YES
to NO
In MySQL, you can do this easily with conditional aggregation:
select sum(my_bool = 1) as yes, sum(my_bool = 0) as no
from table t;
The percentage is very easy:
select sum(my_bool = 1) as yes, sum(my_bool = 0) as no, avg(my_bool = 0)
from table t;
However, your value suggests you are looking for a ratio, not a percentage. For that, you need to be careful about divide by zero:
select sum(my_bool = 1) as yes, sum(my_bool = 0) as no,
(case when sum(my_bool = 1) > 0 then sum(my_bool = 0) / sum(my_bool = 1)
end)
from table t;