CODEWITHSUNDEEP
sqlmysqlcount
Matt McCormick
Matt McCormick

Reputation: 13200

Count(*) with 0 for boolean field

Let's say I have a boolean field in a database table and I want to get a tally of how many are 1 and how many are 0. Currently I am doing:

SELECT  'yes' AS result, COUNT( * ) AS num
FROM  `table` 
WHERE field = 1

UNION 

SELECT  'no' AS result, COUNT( * ) AS num
FROM  `table` 
WHERE field =  0;

Is there an easier way to get the result so that even if there are no false values I will still get:

----------
|yes | 3 |
|no  | 0 |
----------

Upvotes: 1

Views: 4114

Answers (5)

Arild R
Arild R

Reputation: 144

you are on the right track, but the first answer will not be correct. Here is a solution that will give you Yes and No even if there is no "No" in the table:

SELECT 'Yes', (SELECT COUNT(*) FROM Tablename WHERE Field <> 0)
UNION ALL
SELECT 'No', (SELECT COUNT(*) FROM tablename WHERE Field = 0)

Be aware that I've checked Yes as <> 0 because some front end systems that uses SQL Server as backend server, uses -1 and 1 as yes.

Regards Arild

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332631

Because there aren't any existing values for false, if you want to see a summary value for it - you need to LEFT JOIN to a table or derived table/inline view that does. Assuming there's no TYPE_CODES table to lookup the values, use:

   SELECT x.desc_value AS result,
               COALESCE(COUNT(t.field), 0) AS num
     FROM (SELECT 1 AS value, 'yes' AS desc_value
                UNION ALL
                SELECT 2, 'no') x
LEFT JOIN TABLE t ON t.field = x.value
   GROUP BY x.desc_value

Upvotes: 1

Niels van der Rest
Niels van der Rest

Reputation: 32184

This will result in two columns:

SELECT SUM(field) AS yes, COUNT(*) - SUM(field) AS no FROM table

Upvotes: 2

John Pickup
John Pickup

Reputation: 5105

One way would be to outer join onto a lookup table. So, create a lookup table that maps field values to names:

create table field_lookup (
    field int,
    description varchar(3)
)

and populate it

insert into field_lookup values (0, 'no')
insert into field_lookup values (1, 'yes')

now the next bit depends on your SQL vendor, the following has some Sybase (or SQL Server) specific bits (the outer join syntax and isnull to convert nulls to zero):

select description, isnull(num,0)
from (select field, count(*) num from `table` group by field) d, field_lookup  fl
where d.field =* fl.field

Upvotes: 4

Daniel Egeberg
Daniel Egeberg

Reputation: 8382

SELECT COUNT(*) count, field FROM table GROUP BY field;

Not exactly same output format, but it's the same data you get back.

If one of them has none, you won't get that rows back, but that should be easy enough to check for in your code.

Upvotes: 0

Related Questions