group by case when

declare @t as table
(
  unionID int,
  userID int ,
  orderID int
)
insert into @t(unionID,userID,orderID)
values
(null,1,100),
(null,2,200),
(2,null,200),
(2,1,300),
(null,3,400),
(3,4,400),
(null,5,500),
(5,4,500),
(null,null,500)

select * from @t

 

select 
  case 
    when unionID is not null then unionID 
    else userID 
  end as id
 ,COUNT(orderID) as total
 ,COUNT(distinct orderID) as dis_total
from @t
group by 
  case 
    when unionID is not null then unionID 
    else userID 
  end

 

 

 

select  id
 ,COUNT(orderID) as total
 ,COUNT(distinct orderID) as dis_total
from (
select 
  orderID,
  case 
    when unionID is not null then unionID 
    else userID 
  end as id  
from @t
) as a
group by id