I am not sure I understand your requirement but this hould give you a good starting point.
The trick to placing a subquery in the select clause is that the subquery must return a single value.
This is why an aggregate function such as SUM, COUNT, MIN, or MAX is commonly used in the subquery.
I only supplied the last one to get you going in the right direction. I was not expecting to do everything. However, the following should help;
SELECT status,
(SELECT COUNT(status) as "Users with Status of A"
from B.user_details
WHERE status lIke "A%"
and B.id = A.id),
(SELECT COUNT(status) as "Users with Status of A and Admin"
from C.user_details
WHERE status lIke "A%"
and role like "admin%"
and C.id = A.id)
FROM A.user_details
GROUP BY status