CODEWITHSUNDEEP
sqlpostgresql
Rajee
Rajee

Reputation: 51

I want to perform mathematical operation with the column containing null values

In the above attached picture, I want to perform the operation of (run_tot - lag_data)/run_tot. But how to perform that with null values in lag_data. My code is,

select *, ((running_total-lag_data)/running_total) as per_incr_bal
from
(select *, lag(running_total) over(partition by customer_id
order by m) as lag_data
from
(select *, SUM(total) OVER (
partition by customer_id
order by m
rows unbounded preceding) as running_total
from(
select customer_id, extract (month from txn_date) as m,
sum(case when txn_type = 'deposit' then txn_amount
else -txn_amount
end) as total
from data_bank.customer_transactions
group by customer_id, m
order by customer_id, m) t1) t2)t3
where lag_data is not null

I am getting a div by zero error.

1

Upvotes: 1

Views: 64

Answers (2)

Backstreet Imrul
Backstreet Imrul

Reputation: 100

This code may solve your problem ...

  • Try it
SELECT
    *
    ,((COALESCE(running_total,0)-COALESCE(lag_data,0))/ COALESCE(running_total,0)) AS per_incr_bal
FROM
(
    SELECT
        *, LAG(COALESCE(running_total,0)) OVER(PARTITION BY customer_id ORDER BY m) AS lag_data
    FROM
    (
        SELECT
            *, SUM(COALESCE(total,0)) OVER ( PARTITION BY customer_id ORDER BY m ROWS UNBOUNDED PRECEDING) AS running_total
        FROM
        (
            SELECT
                customer_id, 
                EXTRACT (MONTH FROM txn_date) AS m, 
                sum(CASE WHEN txn_type = 'deposit' THEN txn_amount ELSE -txn_amount END) AS total
            FROM data_bank.customer_transactions
            GROUP BY customer_id, m
            ORDER BY customer_id, m
        ) t1
    ) t2
)t3
WHERE lag_data IS NOT NULL;

Tips: Always use COALESCE() when performing mathematical operations to avoid any NULL values in that column.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270421

There may be a better way to do your calculations. But for that, you would need to ask a question with sample data, desired results, and a clear explanation of what you are doing.

To avoid divide-by-zero, use nullif():

(running_total-lag_data)/nullif(running_total, 0) as per_incr_bal

This returns NULL instead of an error.

Upvotes: 2

Related Questions