如何对位列使用SUM ?

How can use the function SUM() for bit columns in T-SQL?

如何在T-SQL中使用函数SUM()用于位列?

When I try do it as below:

当我尝试这样做时:

SELECT SUM(bitColumn) FROM MyTable;

I get the error:

我得到了错误:

Operand data type bit is invalid for sum operator.

操作数数据类型位对于sum运算符无效。

7 个解决方案

#1

SELECT SUM(CAST(bitColumn AS INT))
FROM dbo.MyTable

need to cast into number

需要转换成数字。

or another solution -

或另一种解决方案,

SELECT COUNT(*)
FROM dbo.MyTable
WHERE bitColumn = 1

#2

You could consider 0 as nulls and simply count the remaining values:

你可以把0看成是空值，只要数一下剩余的值:

SELECT count(nullif(bitColumn, 0))
FROM MyTable;

#3

You can achieve by using CONVERT,

你可以通过转换来实现，

SELECT SUM(CONVERT(INT, bitColumn)) FROM MyTable

#4

You can use CAST and CONVERT function for data type to integer or number data type.

可以将数据类型的CAST和CONVERT函数转换为integer或number数据类型。

Try this code blocks :

尝试以下代码块:

SELECT SUM(CAST(bitColumn AS INT)) as bitColumn
FROM MyTable

或

SELECT CONVERT(INT, bitColumn) 
FROM MyTable

#5

You could use SIGN function:

可以使用符号函数:

CREATE TABLE tab_x(b BIT);
INSERT INTO tab_x(b) VALUES(1),(0),(0),(NULL),(0),(1);

SELECT SUM(SIGN(b))
FROM tab_x;
-- 2

DBFiddle Demo

DBFiddle演示

#6

Somewhat cryptically:

有些模糊:

declare @Foo as Bit = 1;
-- @Foo is a Bit.
select SQL_Variant_Property( @Foo, 'BaseType' );
-- But adding zero results in an expression with data type Int.
select SQL_Variant_Property( @Foo + 0, 'BaseType' );
select Sum( @Foo + 0 );

declare @Samples as Table ( Foo Bit );
insert into @Samples ( Foo ) values ( 0 ), ( 1 ), ( 0 ), ( 0 ), ( 1 ), ( 1 ), ( 1 ), ( 1 );
select Sum( Foo + 0 ) from @Samples;

This certainly doesn't improve readability or maintainability, but it is compact.

这当然不能提高可读性或可维护性，但它是紧凑的。

#7

SELECT SUM(bitColumn * 1) FROM dbo.MyTable

从dbo.MyTable中选择SUM(bitColumn * 1)。

Converts the bit into int, by multiplication, clean and simple

通过乘法将位转换成整数，简洁明了

#1

SELECT SUM(CAST(bitColumn AS INT))
FROM dbo.MyTable