I have two tables.

Table A has a1, a2 and a3 fields[columns].
Table B has b1, and b2 fields[columns].

Columns a1 and b1 are related.

Column a1 has 1000 rows while b1 has 600 rows.
{The number of rows like 1000 and 600 are just examples, I can find the number of rows for Table A and B using the function "COUNT", but this may/not be needed!}

Column b1 has distinct records while a1 doesn't.

Objective:
I want to find out the number of repetition of each values in b1 which are repeated in a1 and store it in a new Table C which will have the following columns b1, b2 and NumberOfRepetition.

Note: Not all values of b1 are in a1, some rows of C.NumberOfRepetition can be zero.

EDIT: This has no join, so only displays results from MyTable (does not include blank rows from any other table)

SELECT B.b1, COUNT(A.A1)
FROM B LEFT JOIN A ON B.B1=A.A1
GROUP BY B.B1

Returns all 600 rows from B, and a count of matching rows in A.
Will show zero if no match is found.

Thank you very much pals/folks.

And if you want to exclude result rows with 0 count, you can either nest the above query into a subselect and filter with "WHERE count > 0", or you add "HAVING COUNT(*) > 0".

pls try like this one

SELECT fieldname,
COUNT(fieldname) AS NumOccurrences
FROM tablename GROUP BY fieldname
HAVING ( COUNT(fieldname) > 1 )