Post reply

I want two decimal places

  • March 18, 2011 at 10:54 am

    #247115

    Hi,

    I am struggling to get two decimal places in an AVG number

    so I wrote

    select

    cast(avg(LOS) as decimal (9,2))

    from the table1

    ---it is giving me the result 9.00

    LOS is an integer...

    not giving any values after decimal.

    Please help.

    Thanks so much,

    Hai

  • March 18, 2011 at 11:09 am

    #1300674

    hbanerje (3/18/2011)

    Hi,

    I am struggling to get two decimal places in an AVG number

    so I wrote

    select

    cast(avg(LOS) as decimal (9,2))

    from the table1

    ---it is giving me the result 9.00

    LOS is an integer...

    not giving any values after decimal.

    Please help.

    Thanks so much,

    Hai

    Because LOS is an integer, when you take the average, it's returning an integer - so you are ending up with no decimal points.

    Try casting LOS as float *before* taking the average - you should find yourself with decimal values now. (eg. AVG(cast(LOS as float))

    -Ki

  • March 18, 2011 at 11:24 am

    #1300685

    thanks, but I am getting tons of values after decimal. how to limit that.

  • March 18, 2011 at 11:26 am

    #1300689

    Can you post a sample table definition and sample data? I built a test bed for this and couldn't get avg(LOS) to return anything but an integer result on my system until I forced a type conversion, so I don't know how to duplicate the results you are seeing.

    -Ki

  • March 18, 2011 at 11:33 am

    #1300692

    Yes, it is an integer result, I want to have the average in 2 decimal places.

    Thanks,

    Haimanti

  • March 18, 2011 at 11:45 am

    #1300707

    Let me try to tackle this from another direction. Based on the information you provided, I set up this data set:

    create table #AVGtest

    (LOS int)

    go

    insert into #AVGtest (LOS) values (8)

    go

    insert into #AVGtest (LOS) values (9)

    go

    insert into #AVGtest (LOS) values (9)

    go

    Now run the following against that data:

    select AVG(LOS)

    , AVG(cast (LOS as float))

    , CAST(avg(cast(LOS as float)) as decimal(9,2))

    from #AVGtest

    You'll get 8, 8.666666666666667, and 8.67 as your results.

    Can you give me a sample set of numbers to insert into that table that results in too many decimals for the third column?

    -Ki

  • March 18, 2011 at 2:19 pm

    #1300804

    Thanks so much it worked!!

  • March 21, 2011 at 5:28 am

    #1301197

    You're welcome. Glad it helped!

    -Ki

Viewing 8 posts - 1 through 7 (of 7 total)

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