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.
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
-- Preparing Sample Data
CREATE TABLE TestTable (ID INT, Flag BIT)
INSERT INTO TestTable (ID, Flag)
SELECT 1, 0
SELECT 2, 1
SELECT 3, 0
SELECT 4, 1
Following script will work fine:
-- This will work fine
However following generate error:
-- This will generate error
The workaround is to convert or cast the BIT to INT:
-- Workaround of error
SELECT SUM(CONVERT(INT, Flag))
Clean up the setup
-- Clean up
DROP TABLE TestTable
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)