Count(*) with 0 for boolean field

Question

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 |
----------

Answer 1

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.

Answer 2

Hi, 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

Answer 3

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

Answer 4

This will result in two columns:

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

Answer 5

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