CODEWITHSUNDEEP
sql-servergroup-bymaxbitcoalesce
jayesh kshirsagar
jayesh kshirsagar

Reputation: 23

How to group by using bit column?

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?

Upvotes: 0

Views: 1580

Answers (2)

AGaur
AGaur

Reputation: 305

It seems you are over thinking tings, you can do it with simple query as below:

select max(cast(quota) as int) as max_val, id
from test
group by id

Upvotes: -2

Dale K
Dale K

Reputation: 27471

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

Upvotes: 3

Related Questions