I am trying to get the average occupation rate of 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 bellow.
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 |
CodePudding user response：
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