convert whole no. to float

• Nuts

  SSCrazy

  Points: 2547

  More actions

  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

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  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)
  

• Paul White

  SSC Guru

  Points: 150467

  More actions

  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

• Nuts

  SSCrazy

  Points: 2547

  More actions

  April 21, 2010 at 4:02 pm

  #1155735

  Thanks Guys

  It works well now :):-P