Post reply

case when / case when ...division by 0 error..help!!

  • July 17, 2015 at 1:45 pm

    #309951

    Hi all. So the issue is that there are going to be situations where the denominator is 0. Is there a way to avoid this error and either return a 0 or null in those situations?

    I feel like this is probably a walk in the park for you folks, but a newb has to reach out to the pros when he needs it lol.

    Getting the division by 0 error.

    ,SUBSTRING((COUNT(DISTINCT CASE WHEN d.attribute_value IN ('iPhone','Android') THEN A.id ELSE NULL END)::float/COUNT(DISTINCT CASE WHEN b.activity_name ='Open Email' THEN A.ID ELSE NULL END)::float),1,6) Mobile_Percent

  • July 17, 2015 at 1:57 pm

    #1813427

    There are 2 possible options:

    Instead of using COUNT, use SUM which can return 0.

    ,SUBSTRING((SUM(DISTINCT CASE WHEN d.attribute_value IN ('iPhone','Android') THEN 1 ELSE 0 END)

    /SUM(DISTINCT CASE WHEN b.activity_name ='Open Email' THEN 1 END)),1,6) Mobile_Percent

    Option 2 is to use NULLIF function.

    NULLIF(COUNT(DISTINCT CASE WHEN b.activity_name ='Open Email' THEN A.ID ELSE NULL END), 0)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply