Divide by zero error encountered when using window function sum

Question

When running the below query in SQL Server am getting an error:

Divide by zero error encountered.

select ID, CID, PTYPE, GW,PPE
    , payPercentage = SUM(GW) * 100.0 / SUM(SUM(GW)) OVER (partition by ID, PPE)
from pw_part
where ID ='001014055'
group by ID,CID,PPE,PTYPE,GW

payPercentage=SUM(GW) * 100.0 / SUM(SUM(GW)) OVER (partition by ID, PPE) is causing the error

If I change SUM to

ISNULL (SUM(gross_wages) * 100.0 / NULLIF(SUM(SUM(gross_wages)),0),0) OVER (partition by ssn, pay_period_ending)) as pctg

I am facing the following error

The function 'ISNULL' is not a valid windowing function, and cannot be used with the OVER clause.

Thanks in advance

Window function specification includes over( partition by ... order by ... rows between ...) and ends just after this last bracket. Place all this text in the first argument of nullif: nullif(sum(..) over(...), 0) — astentx
NULLIF(SUM(gw) * 100.0 / (SUM(SUM(gw)) OVER (partition by id, ppe)),0) as pctg - this is giving me "Divide by zero error encountered" error — Yum.Vee
No, you divide single summed wage by total. You need to nullif denominator, so ifnull(sum(gw) / /*Should be NULLed for zero*/nullif(sum(sum(gw)) over(), 0), 0) — astentx
It says - [ 'ifnull' is not a recognized built-in function name] -- am running this on sqlserver — Yum.Vee
NM... got it... it is IS NULL ... thank you for the help... this worked .... isnull(SUM(GW) * 100.0 / nullif(SUM(SUM(GW)) OVER (partition by ID, PPW), 0), 0) as pctg — Yum.Vee

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-05-15T02:48:24

You only need nullif() in the denominator:

SUM(GW) * 100.0 / NULLIF(SUM(SUM(GW)) OVER (partition by ID, PPE), 0) as percentage

In fact, you don't want NULLIF() in the numerator because that might return NULL values that should really be 0.

Divide by zero error encountered when using window function sum

1 Answers