How to get the true/false count from a bit field into two separate columns.

Question

I need to create a query that will sum the number of True(1) and False(0) into two separate columns from one bit field.

I'm joining 3 tables and need it to be something like:

Attribute | Class | Pass | Fail

I will be grouping on Attribute and Class.

Answer 1

Something like this:

SUM(CASE WHEN ColumnName = 1 THEN 1 ELSE 0 END) AS Pass, SUM(CASE WHEN ColumnName = 0 THEN 1 ELSE 0 END) AS Fail

Answer 2

Another option would be

SELECT Attribute, Class
       COUNT(CASE WHEN ColumnName = 1 THEN 1 END) Pass,
       COUNT(CASE WHEN ColumnName = 0 THEN 1 END) Fail FROM YourTable 
GROUP BY Attribute, Class

Answer 3

SELECT
    Attribute,
    Class,
    SUM(CASE BitField WHEN 1 THEN 1 ELSE 0 END) AS [Pass],
    SUM(CASE BitField WHEN 0 THEN 1 ELSE 0 END) AS [Fail]
FROM 
    Table
GROUP BY
    Attribute,
    Class

Answer 4

try:

declare @table table (columnName bit)
insert into @table values (1)
insert into @table values (1)
insert into @table values (1)
insert into @table values (1)
insert into @table values (1)
insert into @table values (0)
insert into @table values (0)
insert into @table values (0)
insert into @table values (0)

SELECT
    SUM(CASE WHEN ColumnName = 1 THEN 1 ELSE 0 END) AS True1
  , SUM(CASE WHEN ColumnName = 0 THEN 1 ELSE 0 END ) AS False0
from @Table

OUTPUT:

True1       False0
----------- -----------
5           4

(1 row(s) affected)