Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Query giving division by zero error in PostgreSQL

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.

like image 550
AKIWEB Avatar asked Mar 05 '14 19:03

AKIWEB


People also ask

How do you solve division by zero in PostgreSQL?

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.

How do you stop division by zero?

Arguably the cleanest (mathematically) method to avoid divide by zero errors is to multiply quantities, rather than dividing one by the other.

How do I divide values in PostgreSQL?

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.


2 Answers

use NULLIF

something/NULLIF(column_name,0)

NULLIF(col,val) is shorthand for

CASE WHEN col=val THEN NULL ELSE col
like image 198
diego matos - keke Avatar answered Sep 30 '22 17:09

diego matos - keke


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;
like image 39
Houari Avatar answered Sep 30 '22 18:09

Houari