How can I use SUM for bit columns?

Question

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.

Solution 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

Solution 2

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

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

Solution 3

SELECT SUM(bitColumn * 1) FROM dbo.MyTable

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

Solution 4

You can achieve by using CONVERT,

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

Solution 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

View more solutions

Comments

[Bruno Pessanha]

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!

[Xiaoyu Lu]

This seems to be the most simple and elegant solution among all.