sql records = 0

Asked

Viewed 45 times

0

want to show qtos customers I have in every city this sql works

but for example, if in Sao Paulo I don’t have any customer registration, it simply hides the city from my report how do I get zero customers he shows: São Paulo 0

SELECT count(*) as total, municipio.nome FROM clientes 
inner join municipio on clientes.codmunicipio = municipio.codigo
group by codmunicipio
sql group-by

  • inner join only brings the existing lines in both tables, for this you need to make a Join that brings everything from municipality, even if there are no lines in customers, can use a right join. This will bring null, hence can convert to zero depending on the database you are using

2 answers

2


--I do this trick, data that "doesn’t exist" -applies best to more complicated sqls --in this case an Outer Join solves as well

select sum(total) total,nome 
from   (SELECT count(*) as total, municipio.nome 
        FROM clientes 
        inner join municipio on clientes.codmunicipio = municipio.codigo
        group by codmunicipio
        union all
        SELECT 0 as total, m2.nome FROM municipio m2) virtual 
group by nome

-2

You can use the COALESCE

SELECT COALESCE(count(*), 0) as total, municipio.nome FROM clientes 
right join municipio on clientes.codmunicipio = municipio.codigo
group by codmunicipio

  • would have to go from municipalities to customers with Outer Join

Browser other questions tagged sql group-by

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