SQL simple rounding

user_2463808 0
Viewed 116

Query:

declare @a float(10);
declare @b float(10);
declare @c float(10);
set @a = 150.50;
set @b = 19;
set @c = 100;
select @a * @b / @c as result
,ROUND(@a * @b / @c, 2) as rounded

Resulting in:

| result  | rounded  |    
----------------------
| 28.595  | 28.59    |

Should the rounded be 28.60? How do I achieve this?

rounding sql-server
4 Answers

You say:

I need two decimal places, result should be 28.60

This sentence is somewhat ambiguous. Thankfully, the number you provide clarifies the case:

  1. You want to round to ONE decimal place. This is done by changing the second parameter of the ROUND function: ROUND(@a * @b / @c, 1) as rounded But, also...
  2. You want to DISPLAY two decimal digits. That does not have to do with the ROUND function, but with formatting functions. One trick is to convert to a decimal type with exactly the amount of decimals you want: convert(decimal(10,2),ROUND(@a * @b / @c, 1)) as rounded
user_58259630

I think you can use the following code

declare @a float(10);
declare @b float(10);
declare @c float(10);
set @a = 150.50;
set @b = 19;
set @c = 100;
select @a * @b / @c as result
,FORMAT(ROUND(@a * @b / @c, 1),'N') as rounded

and result set will be;

+--------+---------+
| result | rounded |
+--------+---------+
| 28.595 |   28.60 |
+--------+---------+
user_121189790

Don't use float, but some exact dataype, like decimal():

declare @a decimal(19,5);
declare @b decimal(19,5);
declare @c decimal(19,5);
set @a = 150.50;
set @b = 19;
set @c = 100;
select @a * @b / @c as result
,ROUND(@a * @b / @c, 2) as rounded

which results

result  rounded
28.595000   28.600000
Arvo_357770 
declare @a decimal(9, 2);
declare @b decimal(9, 2);
declare @c decimal(9, 2);

set @a = 150.50
set @b = 19
set @c = 100

select @a * @b / @c as result
,CAST(ROUND(@a * @b / @c, 2) as decimal(9,2)) as rounded
user_53925680
Related