Average Row [SQL] -

actually i'm bit confused should wrote in subject.

the point this, want average speed01,speed02,speed03 , speed04 :

select    table01.test_no,    table01.speed01,    table01.speed02,    table01.speed03,    table01.speed04, 

i want create new column consists of average -->>

avg(table01.speed01, table01.speed02, table01.speed03,table01.speed04) "average"  

i have tried this, did not work.

from    table01 

so, contain of speed column exist speed02 don't have number others have numbers. speed04 data missing , others exist, 1 data (example: speed01) have data. lets depends on sensor ability catch speed of test material.

it big if can find solution. i'm newbie here. thank ^^

avg sql aggregate function, therefore not applicable. math. average sum divided count:

(speed01 + speed02 + speed03 +speed04)/4 

to deal missing values, use nullif or coalesce:

(coalesce(speed01, 0) + coalesce(speed02, 0) + coalesce(speed03, 0) + coalesce(speed04, 0)) 

that leaves denominator. need add 1 every non null. example:

(coalesce(speed01/speed01,0) + coalesce(speed02/speed02,0) + ...) 

you can use case, depending on supported sql dialect, avoid possible divide 0:

case when speed01 null 0 else 1 

or can normalize data, extract speeds 1:m relation , use avg aggregate, avoiding these issues. not mention possibility add 5th measurement, 6th , on , forth!