Search code examples
sqloracle-databaseweighted-average

finding average by ignoring null values from denominator in oracle sql

we have table like enter image description here

now we have to calculate the average on the base of weightage for col1,col2,col3,col4 as 0.5,1.0,0.5,1.0 respectively so the the formula look like ((col1*0.5)+(col2*1)+(col3*0.5)+(col4*1))/(0.5+1+0.5+1) but if the value of any column is null it should not be considered in denominator e.g for row 2 average formula should be

((0.398160315*0.5)+(nvl(col2,0)*1)+(0*0.5)+(nvl(col4,0)*1))/(0.5+0.5) =0.199080161

calculating in sql i can handle null in numerator by nvl(col1,0) but how can i handle it in denominator

Solution

  • You can use nvl2():

    (0.5*nvl(col1, 0) + 1.0*nvl(col2, 0) + 0.5*nvl(col3, 0) + 1.0*nvl(col4, 0),
 0.5*nvl2(col1, 1, 0) + 1.0*nvl2(col2, 1, 0) + 0.5*nvl2(col3, 1, 0) + 1.0*nvl2(col4, 1, 0)
)

    Usually, I prefer coalesce() to nvl(). But nvl() seems reasonable when used in conjunction with nvl2().