CAST Smallint to Decimal

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  February 19, 2003 at 5:02 pm

  #80313

  Hello,

  I am working on a SQL Server 2000 view in which I need to report the averages of values from a field called 'TestScore'. The datatype of this field is 'smallint'. I need to CAST these average numbers to type 'Decimal', then round the averages up.

  For example, when I do a hard average using a calculator of a select group of data from 'TestScore' I might come up with an average like '40.76' If I use code like 'AVG(TS.TestScore) AS Average_Score', for the same select group of data, the number returned would be '40' when for my purposes, it should be '41'.

  I have tried the following code but still got back a two digit number that was not rounded up:

  CAST(AVG(TS.TestScore)AS Decimal)AS Average_Score

  When I tried this variation of the above code:

  CAST(AVG(TS.TestScore)AS Decimal(4,2))AS Average_Score

  I received the following message:

  (11 row(s) affected)

  Server: Msg 8115, Level 16, State 8, Line 1

  Arithmetic overflow error converting numeric to data type numeric.

  Values in 'TestScore' are always either positive Integers, zero, or negative Integers.

  Thanks.

  CSDunn

• 5409045121009-7368

  SSCoach

  Points: 15740

  More actions

  February 19, 2003 at 5:08 pm

  #449319

  Try ROUND function.

  BOL 2000:

  Returns a numeric expression, rounded to the specified length or precision.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  February 19, 2003 at 6:08 pm

  #449322

  Try instead

  AVG(CAST(TS.TestScore AS Decimal(4,2))) AS Average_Score

  When the average is done it keeps the datatype the same as those pushed into it. Thus a smallint remains a smallint. But if you cast before then it will remain a decimal.

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  February 20, 2003 at 11:19 am

  #449404

  I tried 'AVG(CAST(TS.TestScore AS Decimal(4,2))) AS Average_Score' and was still getting the 'Arithmetic Overflow' error, so I tried a couple of tests:

  1. I made a copy of the table from whence the 'testscore' data came from and populated it with 500 sample records, and changed the datatype of 'testscore' to DECIMAL (4,2).

  2. When I attempted 'AVG(testscore) AS Average_Score' on the copied table, I still received the 'Arithmetic Overflow' error.

  3. I altered the datatype again for 'testscore' to DECIMAL (5,2), and the 'Arithmetic Overflow' error no longer occured when I computed the average, and I am getting 6 decimals places to the right of the decimal (Is this because the records were stored as 'smallint'?).

  4. I went back to the query and implemented the following calculation on the 'testscore' field, and this got me closer to the result I wanted:

  ROUND(AVG(CAST(TS.TestScore AS Decimal(5,2))),0)

  The problem I still have lies with scores that average to zero. On those records, the result comes back as '.000000'.

  I might be able to deal with this in the client application. Otherwise, How can I have zero appear on the left side of the decimal point for records that average out to zero?

  Thanks again!

  CSDunn