Rounding two decimal place works for some

Question

Rounding two decimal place works for some

kd11

Hall of Fame

Points: 3447
More actions
September 10, 2018 at 9:35 pm

#378897

Why some of my records are rounded to two decimal places and others not when I do this:
select emply, cast(sum(ETIME)as float)/3600 as employee_Hours
my results are:
Thomas   140.5
Dave      44.75
Kelly      .56
Sam     1456.876666
Joe      389.4567777
Is it that the sum of Sam & Joe values is so large and the data type is a float, SQL will load/display all the values. Instead of showing
Sam   1456.87
Joe   389.45
If I use Decimal(9,2) I still will get the same results. I guess I can't get the whole number follow by two decimal places.

Viewing 3 posts - 1 through 2 (of 2 total)

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

SQLPirate SSCrazy Points: 2880 More actions · Answer 1

kd11 - Monday, September 10, 2018 9:35 PM
Why some of my records are rounded to two decimal places and others not when I do this:
select emply, cast(sum(ETIME)as float)/3600 as employee_Hours
my results are:
Thomas   140.5
Dave      44.75
Kelly      .56
Sam     1456.876666
Joe      389.4567777
Is it that the sum of Sam & Joe values is so large and the data type is a float, SQL will load/display all the values. Instead of showing
Sam   1456.87
Joe   389.45
If I use Decimal(9,2) I still will get the same results. I guess I can't get the whole number follow by two decimal places.

If you change it to
cast(sum(ETIME)as DECIMAL(9,2))/3600
You're only specifying that the sum(ETIME) is DECIMAL(9,2) and then you divide it by 3600 which could (and apparently does) blow your scale beyond the two decimal places. If you want to cast as decimal, you can wrap the whole thing like this:

CAST(CAST(SUM(ETIME)AS FLOAT)/3600 AS DECIMAL(9,2))

Doing this will round your results. If you just want it truncated you can try using ROUND to do that:
/*examples*/

/*This returns 1.66666666666667*/ SELECT CAST(5 AS FLOAT) / 3

/*This will round to 1.67*/ SELECT CAST(CAST(5 AS FLOAT) / 3 AS DECIMAL(9,2))

/*This one uses ROUND, but truncates to 1.66*/ SELECT ROUND(CAST(5 AS FLOAT) / 3,2,1)

kd11 Hall of Fame Points: 3447 More actions · Answer 2

SQLPirate - Tuesday, September 11, 2018 8:43 AM
kd11 - Monday, September 10, 2018 9:35 PM
Why some of my records are rounded to two decimal places and others not when I do this:
select emply, cast(sum(ETIME)as float)/3600 as employee_Hours
my results are:
Thomas   140.5
Dave      44.75
Kelly      .56
Sam     1456.876666
Joe      389.4567777
Is it that the sum of Sam & Joe values is so large and the data type is a float, SQL will load/display all the values. Instead of showing
Sam   1456.87
Joe   389.45
If I use Decimal(9,2) I still will get the same results. I guess I can't get the whole number follow by two decimal places.
If you change it to
cast(sum(ETIME)as DECIMAL(9,2))/3600
You're only specifying that the sum(ETIME) is DECIMAL(9,2) and then you divide it by 3600 which could (and apparently does) blow your scale beyond the two decimal places. If you want to cast as decimal, you can wrap the whole thing like this:
CAST(CAST(SUM(ETIME)AS FLOAT)/3600 AS DECIMAL(9,2))
Doing this will round your results. If you just want it truncated you can try using ROUND to do that:
/*examples*/
/*This returns 1.66666666666667*/ SELECT CAST(5 AS FLOAT) / 3
/*This will round to 1.67*/ SELECT CAST(CAST(5 AS FLOAT) / 3 AS DECIMAL(9,2))
/*This one uses ROUND, but truncates to 1.66*/ SELECT ROUND(CAST(5 AS FLOAT) / 3,2,1)

Thanks, that works.