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.

Answer

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.