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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Beginning of SQL Server Security
Next Post
SQLAuthority News – Microsoft Whitepaper – AlwaysOn Solution Guide: Offloading Read-Only Workloads to Secondary Replicas

Related Posts

No results found.

7 Comments. Leave new

  • A Count Where Flag=1 would avoid the overhead of the type conversion. I wonder if it would execute quicker?

    Reply
  • Gene Hughson is right…

    Reply
  • Try sum(flag+0)

    Reply
  • hi pinal
    we can also use this way

    — Workaround of error
    SELECT SUM(CASE WHEN Flag = 0 THEN 0 ELSE 1 END)
    FROM TestTable
    GO

    Reply
  • @gene: If you only need that count it is an alternative, otherwise It would require a subquery which is slower

    Reply
  • You can also make use of implicit convertion

    select sum(flag*1) from testtable

    Reply
  • Hi!.
    Try this:

    with x as (select null as a union all select null as a union all select 1 as a)
    select count(distinct x.a) from x

    I know it’s a very weird sql, but it represents a real reuierement,,, :-)

    Then try this other:

    with x as (select null as a union all select null as a)
    select count(distinct x.a) from x

    Same error. As you suggest, it runs:

    with x as (select null as a union all select null as a)
    select count(distinct convert(varchar, x.a)) from x

    Reply

Leave a Reply

Menu