how to select zero Count

Question

have these tables (example)

Municipio (id, nome)
1, Santos
2, Sao Paulo
3, Rio de Janeiro
4, Florianopolis
5, Porto Alegre
6, Natal

Cliente (id, nomecliente, cidade)
1, Joao, 1
2, Pedro, 3
3, Maria, 3
4, Julio, 3
5, Mario, 1

I need to count all municipalities that have zero registered customers i executed the code below and would only sort by number of customers, but cities with customers = 0 it does not show in return, as I do to show?

SELECT COUNT(*), cidade FROM cliente GROUP BY cidade

Answer 1

You should search the main table data municipio to ensure that some line will return. After that make the link with LEFT JOIN on the table cliente and then perform the COUNT column-based cliente.id so the 0 count will be shown if there is no client. To filter using the result of an aggregation function, use the clause HAVING:

SELECT m.nome,
       COUNT(c.id) AS quantidade
  FROM municipio m
  LEFT JOIN cliente c ON c.cidade = m.id
 GROUP BY m.nome
HAVING COUNT(c.id) = 0

Resulting in:

| nome          | quantidade |
| ------------- | ---------- |
| Florianopolis | 0          |
| Natal         | 0          |
| Porto Alegre  | 0          |
| Sao Paulo     | 0          |

See working on DB Fiddle.

Answer 2

You can use the clause NOT EXISTS to do this. So:

SELECT 
mun.id, mun.nome
FROM municipio as mun
WHERE NOT EXISTS (
    SELECT cidade FROM cliente WHERE cidade = mun.id
)

see working

And if you want the TOTAL. That is the number of cities of this result, you can do it:

SELECT count(*) as total FROM 
( 
    SELECT mun.id, mun.nome
    FROM municipio as mun
    WHERE NOT EXISTS (
        SELECT cidade FROM cliente WHERE cidade = mun.id
    )
) as sub;

see working

Answer 3

Try this way (in SQL Server):

SELECT      M.nome                  AS municipio
        ,   ISNULL(C.clientes, 0)   AS clientes
FROM        Municipio   M
LEFT JOIN   (
                SELECT      cidade
                        ,   COUNT(1) AS clientes
                FROM        Cliente
                GROUP BY    cidade
            )           C ON C.cidade = M.id

So it returns the total of customers associated with municipalities.

If only those municipalities with 0 cities, so just put a clause WHERE:

WHERE ISNULL(C.clientes, 0) = 0

In Mysql the ISNULL would have to be replaced by IFNULL:

IFNULL(C.clientes, 0)