How can I sum a bit column?

Question

There is a view as follows:

select t1.PID,t2.*,1 AccessCount 
from Table1 t1 inner join Table2 t2 on t1.id=t2.id

Here, AccessCount column's data type is considered as 'int' by default by SQL server.

But when i apply sum operator on that column, it is saying Sum operator can't be applied on bit type of operand1 and this is not reproducible on all server systems.

Is there any impact of server system configuration on the query execution?

What does server configuration have to do with a clear query error message? Besides, there is no "by default" type. If the error says the type is bit, the type is bit. Post the *actual* query and table schemas. The view doesn't help at all. If the behaviour is different from one server to another, it means the *tables* are different — Panagiotis Kanavos, Jun 16 '16 at 11:38
This query, *as given*, can *never* yield an error of the kind you describe, because it does not contain `SUM`. And `SELECT 1 AccessCount` will *always* yield a column of type `INT` and never a `BIT`, so simply wrapping it in a `SUM` will do nothing either. Post the actual code that's causing you trouble, and the way you're accessing the results. With a view, always try calling `sp_refreshview` first to ensure the metadata isn't out of date. — Jeroen Mostert, Jun 16 '16 at 12:03
Re "Is there any impact of server system configuration on the query execution?": I am not aware of any settings / that would affect this. The cause is some difference between your queries on different systems. — Alex, Jun 16 '16 at 12:52

score 3 · Answer 1 · answered Jun 16 '16 at 11:53

3

U can use these three methods  
    DECLARE @TEST TABLE (GROUP_ID INTEGER, STATUS BIT)
    INSERT INTO @TEST
    SELECT 1, 1 UNION ALL SELECT 1, 0 UNION ALL
    SELECT 2, 1 UNION ALL SELECT 2, 1



    SELECT
            SUM(CAST(STATUS AS INT)) AS '1ST METHOD',
            SUM(CASE(STATUS) WHEN 1 THEN 1 ELSE 0 END) AS '2ND METHOD',
            COUNT(NULLIF(STATUS,0)) AS '3RD METHOD'
    FROM 
            @TEST

answered Jun 16 '16 at 11:53

Chanukya

5,833
1
22
36

can u use these quries – Chanukya Jun 16 '16 at 11:55

score 2 · Answer 2 · answered Jun 16 '16 at 11:51

2

Run below mentioned query to resolve your issue.

SELECT SUM(CONVERT(INT, AccessCount)) FROM Table1

This query will first convert the value into int and then SUM all the values of the column.

answered Jun 16 '16 at 11:51

Himanshu Jain

518
4
20

How can I sum a bit column?

2 Answers2