CODEWITHSUNDEEP
sqloracle-databaseweighted-average
anil tiwari
anil tiwari

Reputation: 195

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

Upvotes: 1

Views: 652

Answers (2)

VIX
VIX

Reputation: 71

Oracle AVG() function can be used to achieve your purpose. May look at the Oracle Docs for more information on usage. The AVG() function ignores NULL values. For example, the average of 2, 4, and NULL is (2 + 4) /2 = 3. Hope that helps.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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

Upvotes: 1

Related Questions