Return the row of a table when the value of count (field) is 0 in MySQL

Question

Return the row of a table when the value of count (field) is 0 in MySQL

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (-1 votes)

0

I need that even when the search does not generate results, the value 0 is displayed, 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;

mysql sql query null

asked by anonymous 11.03.2018 / 02:25

3 answers

Configuration Instance Tomcat / NGinx java sprintg Working with dates from MySQL / PHP

score 0 · Answer 1

use 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 )

score 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

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

score -1 · 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 can help you.