How to get the average from multiple columns

I saw this answer how do I select AVG of multiple columns on a single row but I have some rows which is the value is 0 and it’s throwing an error division by zero when I try to use his

select (COALESCE(q1,0)+COALESCE(q2,0)+COALESCE(q3,0)+COALESCE(q4,0) / (COALESCE(q1/q1,0)+COALESCE(q2/q2,0)+COALESCE(q3/q3,0)+COALESCE(q4/q4,0) from table1

ex.

q1  q2  q3   q4
10  5   NULL 0
8   5   5    NULL
5   5   5    5
--------------
7.5
6
5

Answer

In order to resolve the task, you can use CASE expression because you cannot divide by zero:

SELECT (COALESCE(q1,0) + COALESCE(q2,0) + COALESCE(q3,0) + COALESCE(q4,0)) / COALESCE((COALESCE(q1/(CASE WHEN q1 is null or q1= 0 then 1 else q1 end),0) +  COALESCE(q2/(CASE WHEN q2 is null or q2 = 0 then 1 else q2 end),0) +  COALESCE(q3/(CASE WHEN q3 is null or q3 = 0 then 1 else q3 end),0) +  COALESCE(q4/(CASE WHEN q4 is null or q4 = 0 then 1 else q4 end),0)),1)
 FROM table1

dbfiddle

It is added extra COALESCE because the sum of the divisor can be zero as well