Select count from second table based on initial select?

Something like: SELECT t1. AccountId, t1. ReferenceId, COUNT(t2.

AccountId) FROM Table1 t1 LEFT JOIN Table2 t2 ON t1. AccountId = t2. AccountId AND t1.

ReferenceId = t2. ReferenceId GROUP BY t1. AccountId, t1.

ReferenceId should work. The trick is to group by both key values so you can aggregate over other values. In this case you want to simply count values from other rows (you could also sum or average values from the grouped-by rows.).

You would need an OUTER join to return those for which the count is zero. – Martin Smith Mar 23 at 13:37 You should use a LEFT JOIN for what the OP asked, a JOIN doesn't give the right results – Lamak Mar 23 at 13:38 You're right, I corrected my answer. – Ronald Wildenberg Mar 23 at 13:48.

SELECT T1. AccountId, T1. ReferenceId, COUNT(T2.

ReferenceId) AS Cnt FROM Table1 T1 LEFT JOIN Table2 T2 ON T1. AccountId = T2. AccountId AND T1.

ReferenceId = T2. ReferenceId GROUP BY T1. AccountId, T1.ReferenceId.

Sample data declare @tbl1 table (AccountId INT, ReferenceId int, Name varchar(20)) declare @tbl2 table (AccountId INT, ReferenceId int) insert into @tbl1 select 1, 10, 'White' insert into @tbl1 select 2, 20, 'Green' insert into @tbl1 select 3, 30, 'Black' insert into @tbl1 select 3, 40, 'Red' insert into @tbl2 select 1, 10 insert into @tbl2 select 1, 10 insert into @tbl2 select 2, 20 insert into @tbl2 select 3, 30 Query select t. AccountId, t. ReferenceId, t.Name ,(select COUNT(*) from @tbl2 t2 where t.

AccountId = t2. AccountId and t. ReferenceId = t.

ReferenceId) as countt from @tbl1 t.

– Brian Driscoll Mar 23 at 13:42 @Brian you are right. Just to give a different approach but not good. Answers with LEFT JOIN and Group BY are better.

– Muhammad Kashif Nadeem Mar 23 at 13:43.

SELECT t1. AccountId, t1. ReferenceId, COUNT(t2.

AccountId) FROM Table1 t1 LEFT JOIN Table2 t2 ON (t1. AccountId=t2. AccountId AND t1.

ReferenceId=t2. ReferenceId) GROUP BY Table1. AccountId, Table1.ReferenceId.

This filters the results of Table1 that don't have a match on table2, it should be a LEFT JOIN – Lamak Mar 23 at 13:37 @Lamak you are correct... updating now, thanks! – Brian Driscoll Mar 23 at 13:38 Also I don't think COUNT(t2. *) is valid syntax – Martin Smith Mar 23 at 13:39 @Martin it is valid; I use it frequently.

– Brian Driscoll Mar 23 at 13:40 I'll rephrase. I know that it isn't valid syntax. Try select status,COUNT(t2.

*) from master..spt_values join sys. Objects t2 on number=object_id group by status gives Incorrect syntax near '*'. – Martin Smith Mar 23 at 13:41.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.