Left Join with coalesce

1

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

I'm trying like this: But it did not work, if taking the coalesce works but it counts the 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
    
asked by anonymous 01.10.2017 / 22:36

1 answer

2

I was able to:

SELECT a.sigla, COALESCE (COUNT (number), 0) as FROM FROM sectors to left join sservice b on b.idsetor = a.idsetor and b.ano = '2017' and b.status = 'Canceled' WHERE a.active = 'Yes' group by a.sigla

    
01.10.2017 / 22:48