SQL SERVER – Fix: Error: 8117: Operand data type bit is invalid for sum operator
- by pinaldave
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)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
