Pre-filtering on right table in a LEFT JOIN
For example you can do it this way:

select p.id, p.name, 
COALESCE((select count(*) from child where p.id = pid), 0) AS cnt 
from parent AS p
where name like '%xyz%'


or this:

select p.id, p.name, 
count(c.pid) AS cnt 
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id, p.name


if you need more columns from parent:

select p1.*, CTE.cnt
from parent p1
join (select p.id
count(c.pid) AS cnt 
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id) as CTE on p1.id = CTE.id