sql - Query result in Where clause in sqlite

Question

Table:

id  actor   result
1   Aman    20
2   Aman    23
3   Ston    21
4   halazon 21
5   klawi   20
6   halazon 14

I want to query actors by first letter and how many times they are in the table I tried this but no success:

Query:

select actor, count(*) from actors 
where actor = (select actor from actors
where actor like 'A%' GROUP by actor order by actor ASC)

Expected Result:

Aman 2

Answer 1

I don't see the point for a subquery - plus, it will fail if more than one actor has a name that starts with an A (the subquery will return more than one row, which cannot be used along with =).

If think you want direct filtering:

select actor, count(*) as cnt
from actors 
where actor like 'A%' 
group by actor 
order by actor