CODEWITHSUNDEEP
sqlsql-servert-sql
SQLNewUser
SQLNewUser

Reputation: 17

T-SQL round down to 2 decimals

I have a numeric column, and each number has at least 5 decimals. The examples are in below.

How to round the column off to 2 decimals, while maintaining the sum of the first 3 numbers and sum of the last two numbers equal to 100.00 in SQL Server?

Numbers
22.41489
57.34326
20.24185
73.50463
26.49537

I have tested some sql. For example, 

select cast(round('22.41489',2) as decimal(18,2)) as a, cast(round('57.34326',2) as decimal(18,2)) as b
,cast(round('20.24185',2) as decimal(18,2)) as c, cast(round('73.50463',2) as decimal(18,2))  as d 
, cast(round('26.49537',2) as decimal(18,2)) as e

The sum of a, b, c will be 99.99. Since all the numbers are in the same column, I need to apply the same transformation to all the numbers. I cannot use transformation 1 for the first 3 numbers and apply transformation 2 for the last 2 numbers.

Thanks

Upvotes: 1

Views: 8132

Answers (3)

ColdSolstice
ColdSolstice

Reputation: 465

If I understand your question correctly, you want to round your numbers down to 2 decimal places but you still want the sum to be the same. That's not going to always hold true. For example, if I add these 5 numbers:

 (1.2, 1.2, 1.2, 1.2, 1.2)

I will get 6, but if I round all the numbers first, then take the sum:

 (1.0, 1.0, 1.0, 1.0, 1.0)

I will get 5. This is not a limitation in SQL Server, this is just mathematics.

For your business case I would see if you can "round" and "sum" the numbers on whatever front end you are using (e.g. Reporting Services, Excel, etc) where it has the freedom to truncate decimal places while maintaining the precision behind the scenes if you do aggregate the values.

If that's not an option, return both the rounded/formatted value and the full precision value. Use the rounded/formatted value for UI/Display purposes, and use the full precision value for further aggregation.

CREATE TABLE NumberTable
(
    Number numeric(10,5)
);

INSERT INTO NumberTable
VALUES
 (22.41489),
 (57.34326),
 (20.24185),
 (73.50463),
 (26.49537);

SELECT Number,
    Cast(Round(Number*100,0)/100.00 as numeric(5,2)) RoundedNumber,
    Format(Number,'0.00') FormattedNumber
FROM NumberTable

Upvotes: 0

Marian Nasry
Marian Nasry

Reputation: 819

you can use round() function , even after sum

look at this Fiddle

Upvotes: 0

Aducci
Aducci

Reputation: 26694

You can use Window Functions to add the missing portion to the number that lost the most during rounding. Like this:

declare @T table (grp int, num numeric(10,5))
insert into @T (grp, num) values
(1, 22.41489),
(1, 57.34326),
(1, 20.24185),
(2, 73.50463),
(2, 26.49537)

select
    grp,
    num as original_number,
    round(num, 2) + 
    case when 1 = row_number() over (partition by grp order by num - round(num, 2) desc)
    then (100 - sum(round(num, 2)) over (partition by grp))
    else 0
    end as modified_number  
from @T

Results

1   22.41489    22.42000
1   57.34326    57.34000
1   20.24185    20.24000
2   73.50463    73.50000
2   26.49537    26.50000

Upvotes: 4

Related Questions