CODEWITHSUNDEEP
sqlcount
AlessandroEmm
AlessandroEmm

Reputation: 698

Get Count for each Joined Record

I have 2 tables which I'd like to join and..

A:
ID Otherfields..
1  ...
2
3
4


B:
ID aId  Otherfields..
1  1    ...
2  1
3  2
4  1

So I'm perfectly capable of joining them by a.Id but how do get I get the count for the matches in Table B, like:

a.id  count(b)
1     3
2     1

I figured it must be something with count() over() but cannot recall the exact use.

Thanks!

Upvotes: 1

Views: 66

Answers (2)

Rachcha
Rachcha

Reputation: 8806

You can have

SELECT A.ID, COUNT(b.ID)
FROM A 
LEFT JOIN B ON A.Id = b.aID
GROUP BY A.ID

This will give you all a.IDs that don't exist in b.ID and hence show their count as 0.

E.g.,

ID   Count
1    3
2    1
3    0
4    0

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

You can simply do this:

SELECT 
  A.ID, COUNT(b.ID)
FROM A 
INNER JOIN B ON A.Id = b.aID
GROUP BY A.ID

Upvotes: 3

Related Questions