Avg

[at51178]

Hey Guys

I would like to avg out a column in a query but I want the computer to not see the columns that at 0 so for example

you have (0 + 1+ 2 + 3)/3 "This is basically what I want the computer to do but it is doing this (0+1+2+3)/4 it is adding the row with the 0 in the computation

any help can be appreciated

 

[dds82]

Try this SQL:

Select sum(col) / (Select count(*) from table where col<>0)
from table;

 

[cmmrfrds]

If you want to remove the 0 and the column then make the column null. If this is an Access MDB then use the iif statement, sql server has the nullif() function. By making the column null it will be effectively removed from the computation.

iif(col1=0,null,col1)

iif(expression,true,false) 'please double check syntax