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
– astentx, Commented May 14, 2021 at 23:13
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
– Yum.Vee, Commented May 14, 2021 at 23:18
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
– astentx, Commented May 14, 2021 at 23:22
It says - [ 'ifnull' is not a recognized built-in function name] -- am running this on sqlserver — Yum.Vee
– Yum.Vee, Commented May 14, 2021 at 23:35
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
– Yum.Vee, Commented May 14, 2021 at 23:37

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

1

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.

answered May 15, 2021 at 2:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Divide by zero error encountered when using window function sum

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related