phpmyadmin Question posted in
The official documentation can be found here.

Phpmyadmin – How to find the average of two columns but if one value is null, then only show me the other value?

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.

Example:

Column1 Column2
10 Null
9 15

So in this case I want to see:

Column3
10
12

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

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

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

Tags:

2

Answers


  1. 0

    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. 0

    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 :

    SELECT 
   CASE 
     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
Search