How to count how many records there are in a Onetomany relation?

Question

I have two tables that are related in Onetomany format, example: I have a record of table A that relates to 1 or more records in table B. I’m trying to do a query that returns how many records in table B are related to the same record in table A.

I’m trying this way:

select count (A.Chave) from A inner join B on (A.Chave = B.Chave)

As I am trying the query returns the number of records in table B that have relationship with any record in table A.

How can I fix this ?

Answer 1

I believe I should count "backwards":

SELECT b.chave, COUNT(b.chave)
FROM a
INNER JOIN b ON a.chave = b.chave
GROUP BY b.chave

How you need to know how many occurrences there are in the table B, bring that count on count,