Query giving division by zero error in PostgreSQL

Question

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.

What do you want to display if `request_count+response_count` is zero ? — Houari, Mar 05 '14 at 19:47
If request_count and response count is 0 I want the proportion column should display 0. — AKIWEB, Mar 05 '14 at 19:48
Do that at a higher level, see my answer for a potential solution... — hd1, Mar 05 '14 at 19:49

score 52 · Answer 1 · edited Aug 28 '17 at 20:06

52

use NULLIF

something/NULLIF(column_name,0)

NULLIF(col,val) is shorthand for

CASE WHEN col=val THEN NULL ELSE col

edited Aug 28 '17 at 20:06

CDspace

2,639
18
30
36

answered Feb 17 '16 at 23:16

diego matos - keke

2,099
1
20
11

3

This 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 – Rahul Apr 13 '16 at 21:20
2

I agree, this is the more succinct solution. coding a case statement is unnecessary, and the real answer is Null not 0. – AsAP_Sherb Nov 08 '16 at 21:06

Houari · Accepted Answer · 2014-03-05T19:55:37.853

12

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;

edited Mar 05 '14 at 19:55

answered Mar 05 '14 at 19:49

Houari

5,326
3
31
54

Thank 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. – AKIWEB Mar 05 '14 at 19:58
It depends of what do you want to calculate/display. My answer was to avoid the `divison by zero` error :) – Houari Mar 05 '14 at 20:16
NULLIF 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 – AsAP_Sherb Nov 08 '16 at 21:08
nullIf is better – Jar Jan 09 '20 at 16:13

Query giving division by zero error in PostgreSQL

2 Answers2