mysqlsql

Count boolean field values within a single query


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


Solution

  • 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;