Have Geek Brain

MySQL: count two things in one query?

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

I need to get two counts: the number of rows that have a boolean value of 0, and the number of rows for which it is set to 1. I currently have two queries, one for counting 1 and the other for counting 0 '.

Does MySQL iterate over the entire table when counting rows with a WHERE clause? I am wondering if there is one query that would allow two counters to be used under different conditions?

Or is there a way to get the total count along with the calculated WHERE? This would be sufficient since I would only have to subtract one count from the other (due to the boolean nature of the column). There are no NULL values.

Thanks.

+2


a source to share


4 answers


You can group your records by your logical column and get a 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 for other data types as well, if you need it.

+7


a source


Try the following:



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


a source


If they are all either 0 or 1 and you don't mind 2 lines, as a result, you can group that field and do like this:

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


a source


A simple group proposal should do the trick:

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


a source






More articles:


All Articles