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.

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.

Reference: Pinal Dave (http://blog.sqlauthority.com)

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s