Show All and Count Duplicates MySQL

Question

I have this table:

id   name  
1    AAA
2    BBB
3    BBB
4    BBB
5    AAA
6    CCC

How can I get this?

id   name  count
1    AAA     2
2    BBB     3
3    BBB     3
4    BBB     3
5    AAA     2
6    CCC     1

I want to count duplicates(name), getting all rows, WITHOUT grouping it.

Thank you.

Answer 1

You can use a correlated subquery:

SELECT id, name, (SELECT COUNT(*)
                  FROM mytable AS t1
                  WHERE t1.name = t2.name) AS cnt
FROM mytable AS t2       

Demo here

Answer 2

select t1.id, t1.name, t2.cnt
from your_table t1
join
(
  select name, count(*) as cnt
  from your_table
  group by name
) t2 on t1.name = t2.name

Answer 3

SELECT id, name, (SELECT COUNT(*)
                  FROM demo AS table1
                  WHERE table1.name = table2.name) 
    AS count
FROM demo AS t2