Need help - Divide by 0 error in window equation?-CodePudding

I have the query below and keep getting a divide by 0 error but I'm not sure how to add something to bypass that

    select x.year, x.month, x.period,  gic, coalesce((gic * 1.0 / lag(gic) over (partition by month order by period,year))
- 1,0) as yoy_growth
   from
 (select iap.influencer_id, to_char(order_event_date, 'Mon') as month,
EXTRACT(year from oeo.order_event_date) as year,
EXTRACT(month from oeo.order_event_date) as period,
coalesce(sum(oeo.gross_influencer_commission),0) as gic
from spectrum_curated_zone.orders_ecomm_order_aggregation oeo 
join spectrum_curated_zone.influencer_account_profile iap on 
oeo.influencer_id = iap.influencer_id
where iap.office = 'Dallas'
and oeo.aggregate_channel = 'LTK'
group by 1,2,3,4) x     

group by 1,2,3,4
order by 3 DESC

CodePudding user response：

You can substitute the divisor with NULL if zero (and the return value will then be null, but the query will not fail, and you can deal with NULL result separately). NULLIF function, if available in your DBMS (you didn't tell us what you use) can be used for this purpose:

gic * 1.0 / NULLIF(lag(gic) over (partition by month order by period,year),0)

You can use IIF, CASE WHEN, etc, whatever construct is available to you replace zero with NULL.