mysql returns me the following
codMunicipio count(codMunicipio)
271 7
273 248
274 18332
275 25
276 4
Because no 272 did not return any, it is not displayed. But I need to return the 272 with count 0. How could I do that? my sql is below
SELECT artesao.codMunicipio,count(codMunicipio)
FROM 'artesao'
where artesao.codMunicipio > 270
group by codMunicipio
You have to RIGHT JOIN .
You should have a table of counties. Just make the call.
SELECT
MUNICIPIO.ID,
COALESCE (QTD(CODMUNICIPIO), 0) AS QTD
FROM
ARTESAO
RIGHT JOIN MUNICIPIOS ON MUNICIPIOS.ID = ARTESAO.CODMUNICIPIO
WHERE
MUNICIPIO.ID > 270
GROUP BY
MUNICIPIO.ID
If in your table codMunicipio 272 has codMunicipio equal NULL , you can use IFNULL .
It replaces the NULL values with what you want, in case 0. The syntax is:
{fn IFNULL( expressão, substituição )}
Example of w3schools :
SELECT ProductName, UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products