Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: 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.

    Nickie

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Average of fields in a record

    Originally 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.

  3. #3
    Join Date
    Oct 2003
    Posts
    7
    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

  4. #4
    Join Date
    Oct 2003
    Posts
    7

    I meant to say...

    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.

  5. #5
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •