Ermmm ... how would you describe it? You, me and Scott are all counting the number of non-zero columns as the means of calculating the average - of only the non-zero columns. Or I'm missing something - in which case happy to be enlightened.
Thanks, I do know about that, but I don't have the time, or inclination, to do that for the O/P, I just wanted to alert them to the possibility, if this code is running often / on lots of rows.
I don't read it like that, but you may well be right. My reading is that this cannot be a one-time-operation as otherwise the column would not be correctly populated in future. Of course, in future, it might be populated by a trigger or somesuch, and thus only effecting the changed rows, but even then the trigger might be subjected to many rows (even "all rows in the table") being updated, and therefore, again, I wanted to draw the O/P's attention to the possibly issue of performance. Personally, if I was building this code into a trigger I would avoid updating rows where there would be no change (e.g. some other column in the row might be being changed, which had no impact on the average but would still fire the trigger)
Correct, and I should have pointed that out. Scotts code is more elegant / efficient than mine, it followed mine, and I thought it was better. Yours came after that, so I just wanted to point out to the O/P that IIF was unlikely to be as efficient (I should have explicitly pointed out that I felt that Scotts code was more efficient that yours, and mine, rather than just suggesting that your code was inefficient, per se; my apologies, no offence was intended)