Average of fields in a record

Hi.
I am looking for a simple way to average fields in a record..not rows, but fields, using a procedure. If one of the fields is null, then it needs to be excluded from the average.
Any ideas?
Help would be really appreciated!!
Thanks.
NickieOriginally posted by ngillis
Hi.

I am looking for a simple way to average fields in a record..not rows, but fields, using a procedure. If one of the fields is null, then it needs to be excluded from the average.

Any ideas?

Help would be really appreciated!!

Thanks.

Nickie

If it is permanent table - you can use system tables (syscolumns,sysobjects) for creating dynamic query and calculating.
But, I afraid, there is something wrong with db design if you need to do things like this.|||Hi Snail.

Please explain. My tables are the results of survey information that is entered online by clients. I need to calculate the averages of groups of questions that they answered. One survey is one record in the database, which includes the groups of records.
What would be a better way to do this? I can't change it now as the survey is live..but it would be helpful for future use.

I don't really know much about sysobjects. and help is not that helpful. Any ideas where I can get more information?

Thanks.

Nickie|||Whoops..where I said "One survey is one record in the database, which includes the groups of records." I meant to say the groups of questions.

Thanks.|||Hello Nickie,

an easy way to calculate averages of certain fields would be

select (coalesce(field_1, 0) + coalesce(field_2, 0))/number_of_fields
from table

BUT, this query will replace every NULL value with 0 (or every other number you put in the COALESCE statement).

Maybe this "workaround" will help out. If not post again!

Greetings,
Carsten