MySQL: Count two things in one query?

Question

I have a "boolean" column in one of my tables (value is either 0 or 1).

I need to get two counts: The number of rows that have the boolean set to 0 and the number of rows that have it set to 1. Currently I have two queries: One to count the 1's and the other to count the 0's.

Is MySQL traversing the entire table when counting rows with a WHERE condition? I'm wondering if there's a single query that would allow two counters based on different conditions?

Or is there a way to get the total count along side the WHERE conditioned count? This would be enough as I'd only have to subtract one count from the other (due to the boolean nature of the column). There are no NULL values.

Thanks.

Answer 1

You could group your records by your boolean column and get count for each group.

 SELECT bool_column, COUNT(bool_column) FROM your_table
 WHERE your_conditions
 GROUP BY bool_column

This will obviously work not only for bool columns but also with other data types if you need that.

Answer 2

If they are all either 0 or 1 and you dont mind 2 rows as result you can group by that field and do a count like so:

select field, count(field)
from table
group by field

Answer 3

A simple group clause should do the trick :

SELECT boolField, COUNT(boolField)
FROM myTable
GROUP BY boolField

Answer 4

Try this one:

SELECT
    SUM(your_field) as positive_count,
    SUM(IF(your_field, 0, 1)) as negative_count
FROM thetable