Read Boolean field values ​​in a single query

Question

I like getting the number of rows where a specific field is 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 a result, I expect

 YES | NO | percentage ====================== 3 | 1 | 0.3333 

YES - how many lines, where my_bool true (1), and NO - these are lines with false (0)

percentage indicate percentage from 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 simple:

 select sum(my_bool = 1) as yes, sum(my_bool = 0) as no, avg(my_bool = 0) from table t; 

However, your value suggests that you are looking for a ratio, not a percentage. To do this, you need to be careful about dividing 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; 

Answer 2

 SELECT SUM(IF(my_bool=1, 1, 0)) AS YES, SUM(IF(my_bool=0, 1, 0)) AS NO FROM mytable 

Answer 3

How about this?

 select count(foo.my_bool) as "YES", count(bar.my_bool) as "NO", (count(bar.my_bool) / count(foo.mybool)) as "percentage" from myTable foo left join myTable bar on foo.id = bar.id where foo.my_bool = 1 and bar.my_bool = 0 

Edit: Remember to turn off division by zero, as Gordon Linoff mentioned.

Answer 4

Something like this maybe?

 SELECT sum(my_bool) AS Yes, count(ID)-sum(my_bool) AS No FROM mytable