SQL SERVER – Fix: Error: 8117: Operand data type bit is invalid for sum operator

Here is the very interesting error I received from a reader. He has very interesting question. He attempted to use BIT filed in the SUM aggregation function and he got following error. He went ahead with various different datatype (i.e. INT, TINYINT etc) and he was able to do the SUM but with BIT he faced the problem. The message was Error 8117, operand data type bit is invalid for sum operator.

Error Received:

Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for sum operator.

Reproduction of the error:

Set up the environment

USE tempdb
GO
-- Preparing Sample Data
CREATE TABLE TestTable (ID INT, Flag BIT)
GO
INSERT INTO TestTable (ID, Flag)
SELECT 1, 0
UNION ALL
SELECT 2, 1
UNION ALL
SELECT 3, 0
UNION ALL
SELECT 4, 1
GO
SELECT *
FROM TestTable
GO

Following script will work fine:

-- This will work fine
SELECT SUM(ID)
FROM TestTable
GO

However following generate error:

-- This will generate error
SELECT SUM(Flag)
FROM TestTable
GO

The workaround is to convert or cast the BIT to INT:

-- Workaround of error
SELECT SUM(CONVERT(INT, Flag))
FROM TestTable
GO

Clean up the setup

-- Clean up
DROP TABLE TestTable
GO

Workaround:

As mentioned in above script the workaround is to covert the bit datatype to another friendly data types like INT, TINYINT etc.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.