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

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?

This question and answers originated from www.stackoverflow.com
Question by (1/31/2010 10:49:06 PM)

Answer

 
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
Answer by
Find More Answers
Related Topics  mysql  sql  query  average
Related Questions