Left Join with coalesce

Asked

Viewed 252 times

1

I have the following tables setores(idsetor,sigla,ativo) and another with the services executed called ordem_servico(id,numero,idsetor,). I want to count how many services certain sector canceled and when do not return 0.

I’m trying like this: But it didn’t work, if taking the coalition works but it counts null and returns 1

SELECT a.sigla, COALESCE(COUNT(*) as numero,0) FROM setores a left join ordem_servico b on b.idsetor = a.idsetor and b.ano = '2017' and b.status = 'Cancelada'  WHERE a.ativo = 'Sim' group by a.sigla
mysql sql

1 answer

2

I got:

SELECT a.sigla, COALESCE(COUNT(number),0) the numbers FROM sectors a left Join ordem_servico b on b.idsetor = a.idsetor and b.ano = '2017' and b.status = 'Canceled' WHERE a.ativo = 'Sim' group by a.sigla

Browser other questions tagged mysql sql

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