Count boolean field values within a single query

Question

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

Answer 1

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;

EDIT:

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;