1
mysql returns me the following
codMunicipio count(codMunicipio)
271 7
273 248
274 18332
275 25
276 4
as the 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
1
You have to make a RIGHT JOIN.
You should have a county table. 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
still not displaying the 272, I have the municipality table, I have the code municipality 272, but there is no registered artisan with this municipality, I used this code and the 272 is not displayed SELECT codMunicipio, COALESCE (Count(codMunicipio), 0) AS QTD FROM artisan RIGHT JOIN municipio ON municipio.codigo = artisan.codMunicipio WHERE codMunicipio > 270 GROUP BY codMunicipio
Good solution, why COALESCE and not IFNULL? It seems he does not need "n" arguments.
I put the wrong column, It has to be the Municipality ID column of the table of municipalities. I edited my code. See now...
still not displaying the 272 and 276 that do not return any record, let me try to post an image here do not know if it works <img src="https://1.bp.blogspot.com/-z0j9kc1KR9o/V-wEw9F5iOI/Aaaaaahao/yR8CJnK6J_0FyTku4bLouW8vrUlCdGTFQCLcB/s1600/Capture.JPG" /><img src="https://2.bp.blogspot.com/-yGg2Y5SXdpE/V-wDj34Ba1I/Aaaaaahag/3KuvqHWvpCAsp-qiYXrEClCPfbnA86zgCLcB/s1600/Capture.JPG" /><img src="https://4.bp.blogspot.com/-Cioawu0b3pe/V-wDcWHdSbI/Aaaaaahac/Dtkqcbcuwsl-Cdzgkvqabnsv1f37qclcb/s1600/Capture.JPG" />
if you can click on the 3 links, these are three images
I changed the code. See now
0
If in your table the codMunicipio 272 has with the codMunicipio equal to NULL, you can use the IFNULL.
It replaces the values NULL 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
used the code below and still not displaying the 272, which in case has no record SELECT artisan.codMunicipio,IFNULL(Count(codMunicipio),0) FROM artesao Where craftsman.codMunicipio > 270 group by codMunicipio
@Adriano, try as follows: SELECT artesao.codMunicipio, Count({ fn IFNULL(codMunicipio, 0) }) AS Cod
i made a Count. for 272 has no record. But 272 exists in the codmunicipio table. I did one more test, the 276 was returning 6 record, I changed the codMunicipio of these 6 . now the 276 tb is not displayed. I need it to display with ZERO
tb did not work with fb
@Adriano, put the tables Municipo and Artisan, so it will give to know what you want
@Taisbevalle the difference is irrelevant... http://answall.com/questions/55733/qual-a-diff%C3%A7a-entre-isnull-e-coalesce-numa-search
Browser other questions tagged mysql
You are not signed in. Login or sign up in order to post.
could post a photo of the table? containing the value 272?
– Thomas Erich Pimentel