Need help - Divide by 0 error in window equation?

Question

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, coalesce((commission * 1.0 / lag(commission) over (partition by month order by period,year))
- 1,0) as yoy_growth
   from
 (select iap.influencer, to_char(order_date, 'Mon') as month,
EXTRACT(year from order_event_date) as year,
EXTRACT(month from order_event_date) as period,
coalesce(sum(commission),0) as gic
from orders_table oeo 
join influencer_table iap on 
oeo.id = iap.id
group by 1,2,3,4) x     

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

Answer 1

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.