Tuesday, April 11, 2017

PostgreSQL: Insert zero value rows for missing column value

Source: Insert zero value rows for missing column value
Before:
select month, co_name, count from (
    select distinct co_name, date_trunc('month', from_date)::date as month, count(*) over (partition by co_name, date_trunc('month', from_date)) as count
    from crm_db.ht_co_info where co_name in ('PROTECTION 24', '65')    
) as foo
order by month
After:
with foo as (
    select distinct co_name, date_trunc('month', from_date)::date as month, count(*) over (partition by co_name, date_trunc('month', from_date)) as count
    from crm_db.ht_co_info where co_name in ('PROTECTION 24', '65')
)
select distinct c.co_name, g.month, coalesce(foo2.count, 0)
from (select distinct foo.co_name from foo) c
cross join (select (generate_series('2012-08-01'::date, '2017-04-01'::date, '1 month'::interval))::date) as g(month)
left join foo as foo2 on foo2.co_name = c.co_name and foo2.month = g.month
order by month, co_name