happy55 (12/26/2013)
I have aTable A
id
description
Table B
uniqueId
name
id
where id in b is same as id in A
I want to count all uniqueId in B for each id in A
I write the below query but it is giving value only if the count >0 in table B for any id(i.e if any value exist)
SELECT
a.id,
isnull(count(b.uniqueid),0)
FROM
Table A a
left outer join Table b on b.id = a.id
Group by a.id
Can anyone help me.
Thanks
There must be something more you haven't told us about your data or your requirement. This works for me:
create table #a (id int)
create table #b (id int, uniqueid int)
insert into #a values (1), (2), (3), (4), (5)
insert into #b values (1, 2), (1, 3), (2, 4), (2, 5), (2, 6), (4, 1)
select a.id
,count(b.uniqueid) as n
from #a a
left outer join #b b on a.id = b.id
group by a.id
Result:
idn
12
23
30
41
50
Jason Wolfkill