Search code examples
sql-serveraggregate-functionsand-operator

Aggregate bit column simulating AND operator

I have some row I must aggregate, putting in AND a bit column. I let you see an example... I have a set of data like this:

Id   |  BitValue
----------------
1    |     1 
1    |     1
2    |     0
3    |     1
3    |     0

The result I hope to obtain is:

Id   |  BitValue
----------------
1    |     1 <- = (1 AND 1)
2    |     0
3    |     0 <- = (1 AND 0)

How can I aggregate the column to obtain the result I desire?

I have tried with theese 2 solutions, but no-one works:

SUM(CAST(MyBitField AS INT)),
SUM(CASE(MyBitField) WHEN 1 THEN 1 ELSE 0 END)

Can anyone help me? Thank you

Solution

  • Compare the sum with count if you have only 1 and 0 in that column:

    select Id, case when sum(BitValue) < count(Id) then 0 else 1 end as BitValue 
from mytable group by Id;

    Sql fiddle.

    Thanks for @Larnu's useful comment. I put up another sql fiddle to reflect his concern.