CODEWITHSUNDEEP
sql
Drake
Drake

Reputation: 2703

How to get the number of occurrences in another table

If I have TABLE_A:

A_ID  B_ID
 1     6
 2     6
 3     7
 4     7
 5     7

and TABLE_B:

B_ID B_NAME
 6     B1
 7     B2
 8     B3
 9     B4

How can I find the number of occurrences of B_ID in TABLE_A? Something like this:

select B_ID, B_NAME, total_count from TABLE_B

Where "total_count" is the number of times B_ID is found in TABLE_A so that the result would be:

B_ID B_NAME total_count
 6     B1        2
 7     B2        3
 8     B3        0
 9     B4        0

Upvotes: 0

Views: 58

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Use group by, left join and count:

SELECT B.B_ID, B.B_NAME, COUNT(A.A_Id) As TotalCount
FROM TableB As B
LEFT JOIN TableA As A
    ON B.B_ID = A.B_ID
GROUP BY B.B_ID, B.B_NAME

This is quite a basic query in SQL, and should produce the same result on most if not all relational databases.

Upvotes: 1

Related Questions