How to return dates with a zero count

Asked

Viewed 182 times

2

I’m trying to use the function coalesce to return 0 when my query does not have a return value, but I have tried a thousand and one ways and I cannot 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 function

  • The Coalesce returns for the field of projection the value passed in the second parameter if the first parameter is null, when there is no return, there is no projection field and therefore will not return anything. What is the need to return the field to zero?

  • I want the following to appear... In the case of counting the tuples from zero to some month, currently it does not appear that line, but I want it to appear. Let’s say that in the month of January there was no line, I want it to appear 01.Jan | 0

  • a simple gambiarra would you create a table with the 12 months and make a left Join...

  • Yes @iuristona hehe I had already thought about it, but my system and banks are already in production. I wanted to do something way without the gambiarras.

  • Tried to change inner join for left join?

2 answers

2

In Postgresql it is possible to generate series of dates with the function generate_series. Making a right join of this with the from 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 the conditions have been migrated from the clause where for the clause from preventing them from transforming the outer join in a inner join

0

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 I have in mysql and you will need to adapt to your.

Browser other questions tagged postgresql function

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