MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1_Temp(`word1` varchar(3));
    
INSERT INTO table1_Temp(`word1`)VALUES
    ('aaa'), ('aaa'),
    ('bbb'),
    ('ccc'), ('ccc'), ('ccc'),
    ('ddd');
CREATE TABLE table2_stock(`id` int, `word2` varchar(3), `stock` int);
    
INSERT INTO table2_stock(`id`, `word2`, `stock`) VALUES
    (001, 'aaa', 2),
    (002, 'ccc', 5),
    (003, 'ccc', 3),
    (004, 'ddd', 1);
select t2.id, t1.word1, t2.stock,
       count(t1.word1) Count,
       (select count(*) from table2_stock where word2 = t2.word2) Duplicate
from table1_Temp t1 left join table2_stock t2
on t2.word2 = t1.word1
group by t2.id, t1.word1, t2.stock
order by t1.word1


id
word1
stock
Count
Duplicate


1
aaa
2
2
1

null
bbb
null
1
0

2
ccc
5
3
2

3
ccc
3
3
2

4
ddd
1
1
1