Question 114158 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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
Tags:

3

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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))

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search