How to group by using bit column?

Question

my table data

id  quota  
1  0  
1  NULL  
1  1  
2  0  
2  NULL  
3  NULL  

Result I Except

id  quota  
1  1  
2  0  
3  NULL  

Result I am getting:

id  quota  
1  1  
2  0  
3  0  

Query I am using:

 select id,count(id) count_id,
 case when max(coalesce(quota,0)) = 1 then 1
 when max(coalesce(quota,0)) = 0 then 0
 else null 
 end as quota from forbit group by i  

My data type for column quota is bit.

I want whenever the max in group is 1 give quota as 1, whenever the max in group is 0 give quota as 0 and whenever the max in group is null give quota as null. I basically want to achieve NULL for id 3.

Similar post I saw: Use column of type bit to differentiate group by?

Answer 1

I think just a simple max is what you are looking for, after converting to an int because max doesn't work on a bit.

declare @Test table (id int, quota bit);

insert into @Test (id, quota)
values
(1, 0)
, (1, NULL)
, (1, 1)  
, (2, 0) 
, (2, NULL)  
, (3, NULL);

select id, max(convert(int,quota))
from @Test
group by id;  

Returns:

id  quota
1   1
2   0
3   NULL