Count sql results in table having or not values

What you can do is merge the results of categories that have no active ads to the category count that has active ads with this query :

select
subcategoria.nome,
coalesce(count(anuncios.id)) as quantidade
from subcategoria
left join anuncios on anuncios.id_subcategoria = subcategoria.id
where anuncios.status = 1
group by subcategoria.nome
union
select nome, '0' as quantidade from subcategoria
where subcategoria.id not in (select id_subcategoria from anuncios where status = 1)
order by nome

See this SQL Fiddle .

Add a% w / o AS% qtd in your query and a COUNT(anuncios.*) subcategory.id_subcategory. But looking at it, the JOIN relationship seems to be wrong, but since the question is not the answer above, it looks like this:

    SELECT  
    s.*,
    count(*) as qtd
FROM subcategorias s
LEFT JOIN anuncios a
ON a.id_subcategoria = s.id_subcategoria
GROUP BY s.id_subcategoria

I tried to edit here to be able to get by category requested but the quantity returned is always 1 record

I've edited here to get the requested category, but the quantity always reports as having a record

SELECT s.*, 
COUNT(*) AS qtd 
FROM subcategorias s 
JOIN categorias c 
ON c.id_categoria = s.id_categoria 
LEFT JOIN anuncios a 
ON a.id_subcategoria = s.id_subcategoria 
WHERE s.id_categoria = 6 
GROUP BY s.id_subcategoria