CODEWITHSUNDEEP
sqlsql-servert-sqlaggregate-functions
Bruno Pessanha
Bruno Pessanha

Reputation: 3014

How can I use SUM for bit columns?

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

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.

Upvotes: 61

Views: 57941

Answers (8)

pim
pim

Reputation: 12577

My personal preference is a CASE statement.

SELECT SUM(CASE WHEN bitColumn = 1 THEN 1 ELSE 0 END) FROM MyTable;

Or, if you're really keen on code compression IIF.

SELECT SUM(IIF(bitColumn = 1, 1, 0)) FROM MyTable;

Upvotes: 4

SSS
SSS

Reputation: 321

SELECT SUM(bitColumn * 1) FROM dbo.MyTable

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

Upvotes: 28

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

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

Upvotes: 3

HABO
HABO

Reputation: 15816

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.

Upvotes: 0

ilhan kaya
ilhan kaya

Reputation: 51

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

Try this code blocks : 

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

or

SELECT CONVERT(INT, bitColumn) 
FROM MyTable

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44316

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

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

Upvotes: 22

Pedram
Pedram

Reputation: 6508

You can achieve by using CONVERT,

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

Upvotes: 7

Devart
Devart

Reputation: 121912

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

Upvotes: 86

Related Questions