CODEWITHSUNDEEP
sqlsql-servert-sql
Lalalala9999
Lalalala9999

Reputation: 149

Divide by zero error encountered when using group by

I've got the below piece of code in my query:

CAST(COUNT(C.DateChanged) * 100.0 / COUNT(A.LastPassedBackToSalesDate) AS numeric(18, 2)) AS PBTSbutActived

Both fields are of DateTime datatype. The problem is that when I include group by the above piece of code throws this error:

Divide by zero error encountered.

Warning: Null value is eliminated by an aggregate or other SET operation.

I've read some threads here and tried using this:

CAST(COUNT(NULLIF(C.DateChanged, 0)) * 100.0 / COUNT(NULLIF(A.LastPassedBackToSalesDate, 0)) AS numeric(18, 2)) AS PBTSbutActived

However, it didn't help, I still get the same issue as before.

Upvotes: 0

Views: 2096

Answers (3)

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

Another option would be using a case statement,

CASE WHEN COUNT(A.LastPassedBackToSalesDate) <>0 then 
CAST(COUNT(C.DateChanged) * 100.0 / COUNT(A.LastPassedBackToSalesDate) AS numeric(18, 2)) 
ELSE NULL END AS PBTSbutActived

Upvotes: 0

Santil&#237;n
Santil&#237;n

Reputation: 88

If COUNT(A.LastPassedBackToSalesDate) is 0, you can not avoid that error. You should add a HAVING clause to your query:

CAST(COUNT(C.DateChanged) * 100.0 /
       NULLIF(COUNT(A.LastPassedBackToSalesDate), 0) AS numeric(18, 2)
) AS PBTSbutActived FROM .... GROUP BY ... HAVING COUNT(A.LastPassedBackToSalesDate) <> 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

The NULLIF() goes outside the COUNT():

CAST(COUNT(C.DateChanged) * 100.0 /
           NULLIF(COUNT(A.LastPassedBackToSalesDate), 0) AS numeric(18, 2)
    ) AS PBTSbutActived

That is, you need to check the result of the COUNT(), not the column being counted.

Upvotes: 4

Related Questions