sql - Get average of fields -

sql - Get average of fields -

in sql statement:

select (isnull(tbl1.bromod,0) + isnull(tbl1.bromoform,0) + isnull(tbl1.chlor,0) + isnull(tbl1.dibromoc,0)) / 4 ) tbltruck

i'd dynamically alter value 4 number of values not null.

what trying average ignoring null entries.

say if bromod null , others not bull, split 3, 3 have values.

select ( isnull(tbl1.bromod,0) + isnull(tbl1.bromoform,0) + isnull(tbl1.chlor,0) + isnull(tbl1.dibromoc,0) ) / isnull( nullif( convert(int,convert(bit,isnull(tbl1.bromod,0))) + convert(int,convert(bit,isnull(tbl1.bromoform,0))) + convert(int,convert(bit,isnull(tbl1.chlor,0))) + convert(int,convert(bit,isnull(tbl1.dibromoc,0))) ,0 ) ,1 ) tbltruck tbl1

so,neat trick, #s other 0 convert true when cast bit. so, nulls , 0's become 0 , other values become 1. sum , gives looking for. solution handles split 0 issue arise if values null/zero.

sql sql-server