Avg distinct if condition

[dseelam]

Hello All,

I am trying to write an script for distinct avg if values are greater then zero its working fine with this statement 

avg(DISTINCT(IF(AA>0,AA))AS AA,

below statement is working fine but its including zero when its averaging,how to incorporate greater then zero condition to below statement?

avg(DISTINCT( If(((A * 1) * (B * 1))>0 and (C * 1) * (B * 1) >0, ((C * 1) * (B * 1)) - ((A * 1) * (B * 1)),0))) as COMPCOST

Thanks,

D

[sunny_talwar · Accepted solution]

May be if you remove , 0 from the end of the if statement

avg(DISTINCT( If(((A * 1) * (B * 1))>0 and (C * 1) * (B * 1) >0, ((C * 1) * (B * 1)) - ((A * 1) * (B * 1)),0))) as COMPCOST