The problem is in your sub-query - it doesn't know which
name
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