Return the row of a table when the value of Count(field) is 0 in Mysql

Question

I need that even when the search does not generate results, is displayed the value 0, I tried with the following SQL search, but without success:

    SELECT c.categoria as categoria,
           CASE WHEN COUNT(c.categoria) IS NOT NULL THEN count(c.categoria) ELSE 0 END AS quantidade
    FROM anuncio a
        RIGHT JOIN categoria c ON a.categoria = c.categoria WHERE autorizado = '1'
    GROUP BY c.categoria;

Answer 1

use the coalesce as a subselect:

select coalesce(
 (SELECT c.categoria as categoria,
        COUNT(c.categoria)AS quantidade
    FROM anuncio a
        RIGHT JOIN categoria c ON a.categoria = c.categoria WHERE autorizado = '1'
    GROUP BY c.categoria
), 0 ) 

Answer 2

You can structure your query in a different way, look at this example of the stack:

SELECT
       IFNULL(SUM(TOTAL), 0) AS total,
       IFNULL(SUM(5STAR), 0) AS FiveStar, 
       STORE,
       DATE
FROM `table`
WHERE DATE >= '2012-02-24'
GROUP BY TOTAL

This way you will search for the total and will result 0 if you do not find results.

Answer 3

select codigo,count(id) as QUANTIDADE from(
select id,nome from (
select id from categoria
where ...
group by ID) as a
left join tabela on a.id=id
group by id,nome) as b
left join tabela on b.id=id 
group by código, id

I think this might help you