Post reply

Get the Average of Columns in a Row

  • July 21, 2009 at 10:33 pm

    #212467

    Dear All,

    I need help on something.

    I have a table with this information as shown below.

    I want to add 2 new columns that would store the 3 yrs average for this Hotel and the 3 yrs average for All Hotels for each row.

    My initial thinking that I would need a cursor to do this.

    Can anyone please suggest on how to go about this?

    Thanks!

  • July 21, 2009 at 10:57 pm

    #1028091

    Hi,

    try with this

    create table #temp

    (

    slno int,

    name1 varchar(10),

    AVR1 int,

    AVR2 int,

    AVR3 int

    )

    insert into #temp

    select 1,'ABCD',5,15,25

    union all

    select 2,'BCDE',10,20,30

    union all

    select 2,'CDEF',20,30,40

    alter table #temp add TOTALAVR int

    update #temp

    set TOTALAVR = ((AVR1+AVR2+AVR3)/3)

  • July 22, 2009 at 1:03 am

    #1028107

    Some of my columns have NULL values in the Avg This Hotel Year and Avg All Hotels.

    How do I handle this case?

    Also I want to ignore the NULL values in the calculation of the Avg.

  • July 22, 2009 at 1:12 am

    #1028108

    Hi,

    update #temp

    set TOTALAVR = ((isnull(AVR1,0)+isnull(AVR2,0)+isnull(AVR3,0))/3)

  • July 22, 2009 at 1:19 am

    #1028112

    Thanks!

    But I would like to ignore the NULL columns in my divisor

    e.g. TOTALAVR = (ISNULL(AVR1,0) + ISNULL(AVR2,0) + ISNULL (AVR3, 0) / (Count Only NON-NULL Columns)

  • July 22, 2009 at 2:02 am

    #1028122

    Hi,

    update #temp

    set TOTALAVR = ((isnull(AVR1,0)+isnull(AVR2,0)+isnull(AVR3,0))

    /(case when (AVR1 is null)or

    (AVR2 is null)or

    (AVR3 is null) then 2

    when (AVR1 is null and AVR2 is null)or

    (AVR1 is null and AVR3 is null)or

    (AVR2 is null and AVR3 is null) then 1

    else 3 end))

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply