Select count from second table based on initial select

Table 开发者_运维百科1: AccountId, ReferenceId, Name, (lots of other columns)

Table 2: AccountId, ReferenceId, (other columns)

How can I do a select to get the following:

AccountId, ReferenceId, [Count(*) in Table2 where accountId and reference ID match.]
1, AB, 1
1, AC, 0
2, AD, 4
2, EF, 0

etc

Guessing a join, but that gives me values, not a count? Tried adding a count, but get errors?

 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  

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.).

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 

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