The problem is in your sub-query - it doesn't know which
column you're referring to.
You've essentially asked "how many names start with their own first letter?"
, to which the answer is "all of them"
You need to add an alias to the tables, so that you can refer to the column from the correct table:
select name, (select count(*) from tablename As t2 where t2.name like LEFT(t1.name, 1) + '%') from tablename As t1