CODEWITHSUNDEEP
sql-servert-sqldecimalroundingsqlcommand
heymycoder
heymycoder

Reputation: 79

T-SQL / SQL Server : rounding after convert (decimal) SqlCommand

I want to normalize my SQL Server column before adding to datagridview, I did it in my SQL query. But I have a problem.

If the value fully dividing (for example 100/100=1) I don't want to see this value like 1.00000000000000. And if the value is not fully dividing (for example 3/7=0.42857142857), I want to round that value to two digits after rounding (0.43).

This is my code:

string q = "SELECT column1, column2, ((CONVERT(DECIMAL(18, 2), column3) / (SELECT MAX(column3) FROM tablename)) * 100) AS normalizedColumn3 
            FROM tablename .......;

I need to normalize column 3, it's values before normalize between 1 - 2000000. After normalize, values will be between 0.01 - 100.

Normalize formula:

column 3 = ((column 3 / maximum column 3) * 100)

Thank you for answers...

Upvotes: 1

Views: 580

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

You'll have a small matter of data-loss if you only want two decimals. You'll need at least 5 decimals for values between 1 and 2,000,000

Example

Declare @YourTable table (Col3 float)
Insert into @YourTable values 
 (1),(1536),(1000000),(2000000)

Select A.*
      ,NewVal = convert(decimal(10,2), (Col3*100.0) /  ( Select max(Col3) from @YourTable)  )
 From  @YourTable A

Returns

Col3     NewVal
1        0.00     -- At decimal(10,5) you would see 0.00005
1536     0.08
1000000  50.00
2000000  100.00

Upvotes: 2

Xion
Xion

Reputation: 374

I believe you can use ROUND ( numeric_expression , length [ ,function ] ) or SELECT ROUND(CAST (# AS decimal (#,#)),#); to round your decimal.

Here is more info on it :https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-2017

Upvotes: 0

Related Questions