How to calculate a percentage while avoiding a 'division by zero' error in sql?

Question

Before I start I want to first highlight that this is not a sql question per se even though I stated 'in sql'. the reason for that is because I'm creating it in sql. This is a percentage question in whichever programming language. now what I'm trying to do is get a percentage of something. in order to get a percentage the calculation goes as;

amount * 100 / full amount = amount in percentage

eg. 0.30 * 100 / 250 000 = 0.00012%

which means that 0.30 is worth 0.00012% of 250 000.

now where the problem starts is that the above equation in any programming language creates a division by zero error. I think most of us are familiar with what a division by zero error is. I'm not going to waste words explaining. since I'm using sql many would say use a NULLIF function to return null instead of a value but in this case the problem is. the formula I'm writing, for it to work properly, it needs the value(percentage) amount. here is the code.

CREATE OR REPLACE FUNCTION calc_likes_points(p_artist VARCHAR(150))
    RETURNS INT
AS 
$$

DECLARE

INTER          INT;
INTER_2        INT;
AUD            BIGINT;
P_O_E          INT;
P_O_E_2        INT;
P_O_E_D        NUMERIC;
N_E_P_P_L      BIGINT;
ENG            INT;
ENG_LIKES      BIGINT;
V_O_E          SMALLINT;
T_C_A          INT;
T_L            INT;

BEGIN

    /* abbreviations meanings */
    
    --INTER - Interaction
    --INTER - Interaction 2
    --AUD - Audience
    --ENG_LIKES - Engagement likes
    --ENG - engagement
    --V_O_E - value of engagement
    --P_O_E - percentage of engagement
    --P_O_E_D - percentage of engagement decimal
    --N_E_P_P_L - no of engagement per points likes
    --T_C_A - Total calculated amount
    --T_L - total likes

    /* variables */

    --interaction

    WITH TEMP AS(
    SELECT COUNT(no_of_post) AS POST FROM machine_twitter 
    WHERE artist = p_artist
    UNION ALL
    (SELECT COUNT(no_of_post) AS POST FROM machine_facebook 
    WHERE artist = p_artist)
    UNION ALL
    (SELECT COUNT(no_of_post) AS POST FROM machine_instagram 
    WHERE artist = p_artist)
    UNION ALL
    (SELECT COUNT(no_of_post) AS POST FROM machine_youtube 
    WHERE artist = p_artist)
    UNION ALL
    (SELECT COUNT(no_of_post) POST FROM machine_tiktok 
    WHERE artist = p_artist)
    )
    SELECT SUM(POST) FROM temp INTO INTER;

    --audience

    WITH TEMP AS(
    SELECT MAX(no_of_followers) AS FOLLOWERS FROM machine_twitter
    WHERE artist = p_artist
    UNION ALL
    (SELECT MAX(no_of_followers) AS FOLLOWERS FROM machine_facebook
    WHERE artist = p_artist) 
    UNION ALL
    (SELECT MAX(no_of_followers) AS FOLLOWERS FROM machine_instagram
    WHERE artist = p_artist) 
    UNION ALL
    (SELECT MAX(no_of_followers) AS FOLLOWERS FROM machine_youtube
    WHERE artist = p_artist) 
    UNION ALL
    (SELECT MAX(no_of_followers) AS FOLLOWERS FROM machine_tiktok
    WHERE artist = p_artist)
    )
    SELECT SUM(FOLLOWERS) FROM TEMP INTO AUD;

    --likes engagement

    WITH TEMP AS(
    SELECT likes FROM machine_twitter WHERE
    artist = p_artist
    UNION ALL
    (SELECT likes FROM machine_facebook WHERE
    artist = p_artist)
    UNION ALL
    (SELECT likes FROM machine_youtube WHERE
    artist = p_artist)
    UNION ALL
    (SELECT likes FROM machine_instagram WHERE
    artist = p_artist)
    UNION ALL
    (SELECT likes FROM machine_tiktok WHERE
    artist = p_artist)
    )
    SELECT SUM(likes) FROM TEMP INTO ENG_LIKES;

    /* formula */

    /*

    aud / 100 * inter = percentage of engagement
    engagement * percentage = no of engagement per points likes

    */

    /* equation */

    INTER_2 := 100 * INTER;
    P_O_E := AUD / INTER_2;
    SELECT ceiling(P_O_E) INTO P_O_E_2;
    SELECT quote_literal(0.P_O_E_2);
    SELECT CAST ('0.P_O_E_2') AS float) INTO P_O_E_D;
    N_E_P_P_L := ENG_LIKES * P_O_E_D;

    SELECT ENG_LIKES/N_E_P_P_L INTO T_C_A;

    SELECT total_likes(T_C_A) INTO T_L;

    RETURN T_L;

END;
$$ LANGUAGE plpgsql;

now as you can see from the equation section I changed the formula from inter * 100 / aud to aud/ 100 * inter to avoid the division by zero error part. now this stop-gap solution for division by zero creates a different problem for my formula. in order to get the percentage of engagement the output of aud/100*inter needs to be in numeric instead of integer which is what this formula gives us. now what I am looking for is a way to convert it to numeric but I'm struggling because I just cant find a way. the ceiling and quote_literal functions don't work. they create their own errors. Is there a way around this?

`NULLIF` should do the trick. check [This](https://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql) — Nagib Mahfuz, Jul 20 '22 at 12:16
@NagibMahfuz this work in other scenario's but it doesn't work with my formula. it stops it and returns ``NULL`` . which is counterproductive to what I'm trying to achieve. — Houston Mhlongo, Jul 20 '22 at 14:25
When you use `dividend / NULLIF(divisor, 0)` it will return `0` not `NULL`. so `Divide by zero` error will be gone and output will be `0` — Nagib Mahfuz, Jul 21 '22 at 06:20
Your formula `aud/100*inter` results in as integer because each expression/value in it are integers so Postgres performs integer arithmetic. All you need is to force decimal arithmetic. Try `aud::numeric/100*inter` or even just `aud/100.0*inter`. — Belayer, Jul 22 '22 at 03:56

Sonia Valeja · Answer 1 · 2022-07-21T10:56:38.513

0

Modify the interaction query as

    SELECT CASE WHEN SUM(POST)= 0 
                     OR SUM(POST) IS NULL THEN 1
                ELSE SUM(POST)
           END
    FROM TEMP INTO INTER;

edited Jul 21 '22 at 10:56

answered Jul 21 '22 at 10:49

Sonia Valeja

57
3

How to calculate a percentage while avoiding a 'division by zero' error in sql?

1 Answers1