This not a question asked for any interviews.We are trying to develop an Portal, for which we have encountered such a requirement. Just wanted to make the understandability of my question easy, and hence given such a scenario.
You can do it using a recursive query as below.
WITH CNTCTE (Name, FIND_LAST, REMAINDER) AS
,SUBSTR(Name,LOCATE('A',UPPER(Name)) + 1)
WHERE UPPER(Name) LIKE '%A%'
,FIND_LAST + LOCATE('A',UPPER(REMAINDER))
,SUBSTR(REMAINDER,LOCATE('A',UPPER(REMAINDER)) + 1)
WHERE UPPER(REMAINDER) LIKE '%A%'
SELECT Name,COUNT(Name) AS COUNT_A
GROUP BY Name
ORDER BY Name;