SQL 如何将 SUM 用于位列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33938599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use SUM for bit columns?
提问by Bruno Pessanha
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.
操作数数据类型位对于求和运算符无效。
回答by Devart
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
回答by t-clausen.dk
You could consider 0 as nulls and simply count the remaining values:
您可以将 0 视为空值并简单地计算剩余值：
SELECT count(nullif(bitColumn, 0))
FROM MyTable;
回答by SSS
SELECT SUM(bitColumn * 1) FROM dbo.MyTable
Converts the bit into int, by multiplication, clean and simple
将位转换为整数，通过乘法，干净简单
回答by pedram
You can achieve by using CONVERT,
您可以通过使用来实现CONVERT，
SELECT SUM(CONVERT(INT, bitColumn)) FROM MyTable
回答by Lukasz Szozda
回答by ilhan kaya
You can use CAST and CONVERT function for data type to integer or number data type.
您可以使用 CAST 和 CONVERT 函数将数据类型转换为整数或数字数据类型。
Try this code blocks :
试试这个代码块：
SELECT SUM(CAST(bitColumn AS INT)) as bitColumn
FROM MyTable
or
或者
SELECT CONVERT(INT, bitColumn)
FROM MyTable
回答by HABO
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.
这当然不会提高可读性或可维护性，但它很紧凑。
