如何从一个位字段真/假算成两个单独的列(How to get the true/false coun

我需要创建一个查询，将总结真（1）（0）的数量和假成两个单独的列从一个位字段。

我加入3个表，需要的是这样的：

属性| 类| 通过| 失败

我将在其上进行分组属性和类别。

Answer 1:

事情是这样的：

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:

此作品（至少在2008年SQL）

SELECT SUM(Passed + 0) PASS , SUM(1 - Passed) FAIL

我加入0在第一总和走过了从位转换为int，因为你不能直接相加位的短手的方式。

Answer 3:

尝试：

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)

Answer 4:

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

另一种选择是

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

甚至有一个选择：

SELECT 
   Attribute, 
   Class,
   COUNT(BoolColumnName = 1 or NULL) Pass,
   COUNT(BoolColumnName = 0 or NULL) Fail 
FROM Table
GROUP BY Attribute, Class

文章来源: How to get the true/false count from a bit field into two separate columns