I am trying to run the following query which results me postgres error: division by zero
select
request_count,
response_count,
(response_count*100) / (request_count+response_count) AS proportion
from total_dummy_table;
How can I avoid and resolve the division by zero
error?
Tried to fix using the below query but getting the same result as above
SELECT
request_count,
response_count,
(response_count*100) / (request_count) AS proportion
FROM
total_dummy_table
ORDER BY
(response_count*100) /(CASE request_count WHEN 0 Then NULL ELSE request_count END) DESC NULLS FIRST
Please let me know where am I doing wrong, or how can I fix this. Thanks!
Result expectation:
The query should return me something like below:
Request_count | Response_count | Proportion
1603423 | 585706 | 36.52
Quick note: Table total_dummy_table does not have column name proportion
that is the addition to the result which have calculated proportion in it.
If you'd like to handle division by zero gracefully, you can use the NULLIF function. NULLIF takes two arguments: the expression of interest, and the value you want to override. If the first argument is equal to the second, then NULLIF returns NULL; otherwise, it returns the first argument.
Arguably the cleanest (mathematically) method to avoid divide by zero errors is to multiply quantities, rather than dividing one by the other.
In PostgreSQL, the / operator stands for division. If the columns have integer types, PostgreSQL will use integer division. Integer division is division in which the fractional part (remainder) is discarded. For example, in integer division, the result of 5/2 is 2.
use NULLIF
something/NULLIF(column_name,0)
NULLIF(col,val)
is shorthand for
CASE WHEN col=val THEN NULL ELSE col
select request_count,response_count,
case
when
request_count+response_count = 0 then 0
else
(response_count*100) / (request_count+response_count)
end AS proportion
from total_dummy_table;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With