What will be the output of

declare @tbl table(id int identity, refid int)
insert into @tbl
select 0 union all select 1 union all
select 2 union all select 1 union all
select 1

select t.id,t.refid,case when x.cnt is null then 0 else x.cnt end as total
from @tbl t
left join
(
select refid,count(refid) as cnt
from @tbl
where refid <> 0
group by refid
having (count(refid)>0)) x
on t.id = x.refid