Avg distinct if condition

dseelam — Tue, 20 Nov 2018 15:02:25 GMT

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,

Re: Avg distinct if condition

sunny_talwar — Tue, 20 Nov 2018 15:26:53 GMT

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