CODEWITHSUNDEEP
sqlsql-servert-sql
Yum.Vee
Yum.Vee

Reputation: 41

Divide by zero error encountered when using window function sum

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

Upvotes: 0

Views: 667

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271181

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.

Upvotes: 1

Related Questions