PostgreSQL division by zero when ordering

Question

I need to execute this request in postgres, but I could not get rid of this error

    ERROR: division by zero
SQL state: 22012

here is the request:

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

I know that I can add a small value to rates_count, but I get inaccurate values.

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

thank

Answer 1

Use the CASE statement:

SELECT 
    id,
    rates_sum,
    rates_count 
FROM 
    tbl_node  
ORDER BY 
    rates_sum / (CASE rates_count WHEN 0 THEN NULL ELSE rates_count END) DESC NULLS FIRST;

You can also use NULLS LASTif you want.

Answer 2

What about where rates_count != 0?