skip to Main Content

I have a table where I want to find the average value of column1 and column2, but in some cases there may be null values in either columns. In that case, I just want to be shown the value that is NOT null.


Column1 Column2
10 Null
9 15

So in this case I want to see:


I’m still a beginner so my knowledge is pretty limited, also I want to keep it as simple as possible. I’m using phpmyadmin if that makes any difference.

I’ve tried sum(table1+table2)/2 which results in null if one of the values is null.
I’ve also tried

     WHEN table2 IS null 
       THEN SUM(table1) 
     ELSE SUM(table1+table2)/2 

but it just returns the average of rows where there isn’t a null value (I only get back 12, not the 10 as well (refer to the above table))



  1. SUM is for summing values over multiple rows. Here, you just want simple math and replacing nulls with 0 and counting non-nulls

    select ( coalesce(Column1,0) + coalesce(Column2,0) ) /
        ( if(Column1 is null,0,1) + if(Column2 is null,0,1) )
        as Column3
    Login or Signup to reply.
  2. You are near with the case expression.

    There is no need for sum and you have not considered null for column1 .

    Fixing those, the query would be :

         WHEN Column1 IS null THEN Column2 
         WHEN Column2 IS null THEN Column1 
         ELSE (Column1+Column2) / 2 
       END as column3
    FROM myTable

    See example

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