Query giving division by zero error in PostgreSQL
27,869
Solution 1
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;
Solution 2
use NULLIF
something/NULLIF(column_name,0)
NULLIF(col,val)
is shorthand for
CASE WHEN col=val THEN NULL ELSE col
Comments
-
AKIWEB over 3 years
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. -
AKIWEB over 10 yearsThank you Hourari, quick question and very dumb too- here the total count according to me is request_count, out of which how many people responded is a response_count. So my understanding says the total have to be just request_count not the addition of them? correct? I am just curious.
-
Houari over 10 yearsIt depends of what do you want to calculate/display. My answer was to avoid the
divison by zero
error :) -
Rahul about 8 yearsThis should be accepted as the right answer. Its tedious to repeat the denominator. Especially if its a big expression, you will the feel the pain repeating the whole denominator as in @Houari 's answer
-
AsAP_Sherb over 7 yearsI agree, this is the more succinct solution. coding a case statement is unnecessary, and the real answer is Null not 0.
-
AsAP_Sherb over 7 yearsNULLIF is a more appropriate solution to avoid divide by zero errors, and is also a much more simple solution. I recommend the answer from diego
-
Jar over 4 yearsnullIf is better