How can I use SUM for bit columns?

sql sql-server tsql aggregate-functions
Bruno Pessanha picture Bruno Pessanha · Nov 26, 2015 · Viewed 31.2k times · Source

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.

Answer

Devart picture Devart · Nov 26, 2015 
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

Related questions

Multiple aggregate functions in one SQL query from the same table using different conditions

I'm working on creating a SQL query that will pull records from a table based on the value of two aggregate functions. These aggregate functions are pulling data from the same table, but with different filter conditions. The problem that …

Read More
string concatenate in group by function with other aggregate functions

Is it possible to concatenate strings with one or more of other group by function like sum, avg, count etc . Say I have the following table Id Name Order Value 1 a 1 100 2 b 2 200 3 c 1 300 4 d 1 100 5 e 2 300 Now if I want the …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More