Total Count

[NET India]

Hello Friends, I've a table as shown below ID RefID 1 0 2 1 3 2 4 1 5 1 and i want to display it's output as shown below but i'm not getting the way please suggest a way ID RefID Total 1 0 3 2 1 1 3 2 0 4 1 0 5 1 0

[Mycroft Holmes]

This is one solution if the table is small, it requires a seperate sub select for each record

Select A.ID, A.RefID, (Select count(*) from Table where RefID = ID) Total
From Table

For a more efficient method on a large table I would use a LEFT join to itself on TableA.ID = TableB.RefID then use Isnull, a case statement and count to get the same result from a large table.

Never underestimate the power of human stupidity RAH

[Niladri_Biswas]

Try this

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

Output : id refid total

1 0 3
2 1 1
3 2 0
4 1 0
5 1 0

:)

Niladri Biswas