How to get the average from multiple columns-CodePudding

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

CodePudding user response：

You can turn the columns into rows then use avg() as an aggregate on those rows:

select (select avg(x.val) filter (where x.val > 0)
        from (values (t.q1),(t.q2),(t.q3),(t.q4) 
       ) as x(val)) as avg
from the_table t

alternatively this can be expressed with a lateral cross join:

select p.*
from the_table t
  cross join lateral (
     select avg(x.val) filter (where x.val > 0)
     from (
       values (t.q1),(t.q2),(t.q3),(t.q4) 
     ) as x(val)
  ) p

Instead of using avg (x.val) filter(...) you can also use avg(nullif(x.val,0))

CodePudding user response：

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

CodePudding user response：

you are qualescing q4/q4 which is 0/0

i gave it a try and this is the query that worked, converting the value to bool first (true/false) and then converting that to an int is the safer option if you expect a lot of null and zero values:

select (
    COALESCE(q1::real, 0::real) 
    COALESCE(q2::real, 0::real) 
    COALESCE(q3::real, 0::real) 
    COALESCE(q4::real, 0::real)
)
/
(
   COALESCE((q1::bool)::int,0) 
   COALESCE((q2::bool)::int,0) 
   COALESCE((q3::bool)::int,0) 
   COALESCE((q4::bool)::int,0)
) from vals;