CODEWITHSUNDEEP
t-sqlselect
ImmortalStrawberry
ImmortalStrawberry

Reputation: 6101

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?

Upvotes: 0

Views: 313

Answers (4)

Ronald Wildenberg
Ronald Wildenberg

Reputation: 32134

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

Upvotes: 1

Brian Driscoll
Brian Driscoll

Reputation: 19635

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

Upvotes: 0

Kashif
Kashif

Reputation: 14440

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453618

 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

Upvotes: 2

Related Questions