CODEWITHSUNDEEP
mysqlcount
nebs
nebs

Reputation: 4989

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.

Upvotes: 2

Views: 5154

Answers (4)

newtover
newtover

Reputation: 32094

Try this one:

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

Upvotes: 3

Thibault Falise
Thibault Falise

Reputation: 5885

A simple group clause should do the trick :

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

Upvotes: 0

Fabian
Fabian

Reputation: 13691

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

Upvotes: 0

RaYell
RaYell

Reputation: 70414

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.

Upvotes: 7

Related Questions