Reputation: 149
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
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
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
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