MySQL: Count two things in one query?

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.

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.

Try this one:

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

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

A simple group clause should do the trick :

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