Returning dates with zero count

Question

Returning dates with zero count

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I'm trying to use the coalesce function to return 0 when my query does not have a return value, but I have already tried a thousand and one shapes and can not return 0 at all. I want to know where I'm going wrong:

select
    case 
        when date_part('month', p.data_cadastro) = 1 then '01.Jan'
        when date_part('month', p.data_cadastro) = 2 then '02.Fev'
        when date_part('month', p.data_cadastro) = 3 then '03.Mar'
        when date_part('month', p.data_cadastro) = 4 then '04.Abr'
        when date_part('month', p.data_cadastro) = 5 then '05.Mai'
        when date_part('month', p.data_cadastro) = 6 then '06.Jun'
        when date_part('month', p.data_cadastro) = 7 then '07.Jul'
        when date_part('month', p.data_cadastro) = 8 then '08.Ago'
        when date_part('month', p.data_cadastro) = 9 then '09.Set'
        when date_part('month', p.data_cadastro) = 10 then '10.Out'
        when date_part('month', p.data_cadastro) = 11 then '11.Nov'
        else '12.Dez'
    end as mes,
    coalesce(count(*), '0'), 
    s.descricao as situacao
from
    tb_historico p
    inner join
    tb_situacao s on s.id_situacao = p.id_situacao
where
    date_part('year', p.data_cadastro) = 2015
    and s.descricao = 'APROVADO'
    and id_usuario = 10
    and id_tipo_projeto = 1
group by mes, s.descricao
order by mes

postgresql

asked by anonymous 28.04.2015 / 21:25

2 answers

How to make mute start video How to debug ASP.NET applications or Webservices in IIS

score 2 · Answer 1

In Postgresql it is possible to generate series of dates with the function generate_series . By doing a% of this% with the right join of your original query all dates will be returned:

select
    to_char(d.d, 'MM.Mon') as mes,
    s.descricao as situacao,
    count(*) as total
from
    tb_historico p
    inner join
    tb_situacao s on
        s.id_situacao = p.id_situacao
        and date_part('year', p.data_cadastro) = 2015
        and s.descricao = 'APROVADO'
        and id_usuario = 10
        and id_tipo_projeto = 1
    right join
    generate_series (
        '2000-01-01'::date, '2000-12-01', '1 month'
    ) d(d) on date_part('month', d.d) = date_part('month', p.data_cadastro)
group by 1, 2
order by 1, 2

Note that conditions have been migrated from the from clause to the where clause by preventing them from transforming from to outer join

score 0 · Answer 2

You can do something like this:

select p.AcctStartTime, m, substring(date_format(m, '%m.%M'), 1, 6) mes from (
    select m from (
         select 1 m union all select 2 union all select 3 union all select 4 
         union all select 5 union all select 6 union all select 7 
            union all select 8 union all select 9 union all select 10 
            union all select 11 union all select 12) meses) mm 
    left join radacct p on mm.m = date_format(p.AcctStartTime, '%c')

I used a table that I have in mysql and you will need to adapt it to yours.