PostgreSQL division by zero when ordering

Question

i need to execute this query in postgres but i couldn't get rid of this error

    ERROR: division by zero
SQL state: 22012

here is the query :

select id,rates_sum,rates_count from tbl_node  order by rates_sum/rates_count DESC;

i know i can add a small value to the rates_count but i get inaccurate values .

Is there a way to make the postgres ignore this error ,or using if statement to check zeros and replace them with any number. and again the error in the order by clause.

Thanks

Answer 1

Use a CASE statement:

SELECT 
    id,
    rates_sum,
    rates_count 
FROM 
    tbl_node  
ORDER BY 
    rates_sum / NULLIF(rates_count,0) DESC NULLS FIRST;

You could also use NULLS LAST, if you want to.