how to select zero Count

Asked

Viewed 839 times

6

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
  • What the SGBD used?

3 answers

7


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.

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

  • 1

    @Sorack thanks you! I edited!

1

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)
  • Twice? Where did you see the 2nd?

  • 2

    ISNULL depends on the DBMS

  • Yes, I know @Sorack, I assumed it was SQL Server.

  • 1

    Edited response with addition of ISNULL for Mysql.

  • No? But the query runs smoothly. You’re referring to not only returning cities without customers?

  • 1

    Right, in my reply I mentioned exactly that! To return you only cities without customers will have to add the clause WHERE in the end: WHERE IFNULL(C.clientes, 0) = 0

  • 1

    @Joãomartins ah ta... would have to use the clause Where ... got it...

  • @Joãomartins worked > http://www.sqlfiddle.com/#! 9/8557d6/25 =)

  • Take advantage and give an UP in the answers you considered valid and mark one of them as correct :). Why it is important to vote?

  • Forget @Andreicoelho, I saw it wrong! It’s Friday, exhaustion accumulated!

  • @Joãomartins kkkkkk quiet!

Show 6 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.