51

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.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Bruno Pessanha
  • 2,874
  • 4
  • 24
  • 35
  • It depends on what you think a sum of a bit column is supposed to mean. SQL Server doesn't allow it because it's ambiguous because bit columns are often boolean values or bitmasks. It's like asking "What's the value of `Add(True,False)`?" Well, is Add() an AND function or an OR function? – Bacon Bits Nov 26 '15 at 12:26
  • It's just a way to group results together. In my scenario one record has bitColumn True and other records have bitColumn False, but I don't want the grouping to be lost because of that. So I'm summing the "bit" columns and I'm interested if the SUM is greater than 0. – Bruno Pessanha Nov 26 '15 at 12:30
  • @BaconBits ADD usually translates to OR, and MULTIPLY to AND, because they follow similar laws – Arturo Torres Sánchez Mar 23 '18 at 18:13
  • @ArturoTorresSánchez That's because that's how logic gates work where AND is a multiply and XOR is an add, not because that's the only meaningful interpretation of `SUM( )`. It's certainly not clear enough to demand a specific implementation of `SUM()`. – Bacon Bits Mar 23 '18 at 20:53
  • Bits cannot be True or False. Bits can only be 1 or 0. Boolean can be True or False. Summation of bits is not ambiguous. see https://en.wikipedia.org/wiki/Binary_number#Binary_arithmetic – John Henckel Aug 01 '23 at 16:26

8 Answers8

76
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
Devart
  • 119,203
  • 23
  • 166
  • 186
  • 5
    My query is in fact quite more complex than the example on my question. The bitColumn is coming from a joined table and my results are grouped. I don't want to stop returning the results where bitColumn are False. But Solution 1 is perfect. So simple! – Bruno Pessanha Nov 26 '15 at 12:37
24
SELECT SUM(bitColumn * 1) FROM dbo.MyTable

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

Xiaoyu Lu
  • 3,280
  • 1
  • 22
  • 34
SSS
  • 281
  • 2
  • 4
21

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

SELECT count(nullif(bitColumn, 0))
FROM MyTable;
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
7

You can achieve by using CONVERT,

SELECT SUM(CONVERT(INT, bitColumn)) FROM MyTable
Pedram
  • 6,256
  • 10
  • 65
  • 87
4

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;
pim
  • 12,019
  • 6
  • 66
  • 69
3

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

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
ilhan kaya
  • 51
  • 10
0

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.

HABO
  • 15,314
  • 5
  • 39
  • 57