Join two table grouping by and count even when no value found (show as 0)

Question

I want to join two tables counting and grouping the elements in the table A based on the element in the table B. This table can have duplicates (A):

This one is reference table for the output (B):

and I would like to obtain an output like this:

so the output has the same number of rows of the B table but counting the one in A.

I tried to do something like that without success:

SELECT B.id, B.Serial, B.Type, COUNT(A.id)
FROM B
LEFT JOIN A ON A.id = B.id
WHERE B.id = 100
GROUP BY B.Serial, B.Type

someone can help me?

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query - and be sure to clarify your PRIMARY KEY in each case. — Strawberry, Feb 28 '21 at 09:54

score 0 · Answer 1 · answered Feb 28 '21 at 10:01

0

You don't need GROUP BY.

You need a simple subquery.

SELECT *, (SELECT COUNT(*) FROM A WHERE ID = B.ID AND Serial = B.Serial AND Type = B.Type) COUNT
FROM B

answered Feb 28 '21 at 10:01

Zunayed Shahriar

1 Answers1