Post reply

convert whole no. to float

  • April 19, 2010 at 6:47 pm

    #386739

    Hi Frenz

    I have a list of patient count by month and they are whole nos.

    Month Pat_count

    1 300

    2 500

    3 110

    ..

    12 550

    When I calculate the avg patient count, it gives me a whole no. for the avg.

    How can I convert the avg into decimal point??

    Thanks

  • April 19, 2010 at 7:27 pm

    #1154236

    Try this:

    Avg(1.0*pat_count)

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • April 19, 2010 at 11:00 pm

    #1154300

    The Dixie Flatline (4/19/2010)

    Try this:

    Avg(1.0*pat_count)

    Or just Avg(0.0+pat_count) which is just a little less expensive CPU wise.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 19, 2010 at 11:14 pm

    #1154306

    It is more efficient to adjust the data type after the aggregate:

    DECLARE @Example

    TABLE (

    month_id TINYINT NOT NULL,

    patient_count INTEGER NOT NULL

    );

    INSERT @Example (month_id, patient_count) VALUES (1, 100);

    INSERT @Example (month_id, patient_count) VALUES (2, 200);

    INSERT @Example (month_id, patient_count) VALUES (3, 150);

    INSERT @Example (month_id, patient_count) VALUES (4, 325);

    SELECT average = CONVERT(DECIMAL(9,1), SUM(patient_count)) / COUNT(*)

    FROM @Example;

    For the complete set of rules for determining the type of a result, see:

    Precision, Scale, and Length (Transact-SQL)

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • April 21, 2010 at 4:02 pm

    #1155735

    Thanks Guys

    It works well now :):-P

Viewing 5 posts - 1 through 4 (of 4 total)

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