What's the most efficient way to get the horizontal average in a MySQL query?

average, mysql, sql

Solution

select id, (ifnull(one,0) + ifnull(two,0) + ifnull(three,0))/
  ((one is not null) + (two is not null) + (three is not null)) as average from table

Problem

I have the following MySQL-table ``` Id | One | Two | Three ---------------------------- 1 | 10 | 30 | 20 2 | 50 | 60 | 20 3 | 60 | NULL | 40 ``` Edit: Of course the table doesn't need to be NULL by default, but I don't want it to affect the average (so the average is calculated as 50 and not 33,33). I want that to look like this, with a MySQL query: ``` Id | Average ------------ 1 | 20 2 | 43,33 3 | 50 ``` What is the most efficient way to achieve this?

Original source