PostgreSQL: How to avoid divide by zero?

Question

I am trying to get the average occupation rate from table occu_cap but I am getting the error "ERROR: division by zero". There are 0 values in both columns. I've been looking at using NULLIF(column_name,0) but I can't figure out how to implement this into the code below.

SELECT *, AVG((occupancy/capacity) * 100)) AS avg_occupancy_rate
FROM occu_cap
GROUP BY 1,2,3

Sample data and expected result:

occupancy	capacity	avg_occupancy_rate
1232	1630	75.58
0	658	null
0	0	null

Answer 1

The error was caused that capacity was 0 value (which might not allow from math divide), if your expected result is 0 when capacity is 0 from occupancy/capacity

AVG((COALESCE(occupancy / NULLIF(capacity,0), 0) * 100))

Edit

You can try to use CASE WHEN expression to judge the value whether zero then return NULL

AVG(CASE WHEN capacity <> 0 AND occupancy <> 0 THEN ((occupancy::decimal/capacity * 1.0) * 100) END)

If you want to show all of columns you can try to use the window function.

SELECT *,AVG(CASE WHEN capacity <> 0 AND occupancy <> 0 THEN ((occupancy::decimal/capacity * 1.0) * 100) END) OVER(PARTITION BY id)
FROM occu_cap

NOTE

If your occupancy or capacity is not a type of a float-point number we need to CAST that as a float-point number before doing AVG

sqlfiddle