How to SELECT round up number in SQL Server

Posted on

How to SELECT round up number in SQL Server – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server,  case. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.

SQL Problem :

I have a salary table with this column

ANNA             113750
MARRY            124300
BELLA            105100

I want to round up the amount of salary with 2000 or 5000 fractions which is nearby. So for this case it will be like

ANNA             114000
MARRY            125000
BELLA            106000

For additional information, I used MS SQL Server. Please help me to do that. Thanks in advance

Solution :

Divide the salary by the fraction. Use ceiling to round up. Then multiply by the fraction.

Declare @salary table (Employee_name nvarchar(50), Salary money)
Declare @fraction money = 5000

insert into @salary
('ANNA',             113750),
('MARRY',           124300),
('BELLA',           105100)

update @salary
set Salary = ceiling(salary/@fraction)*@fraction

select * from @salary

Unlike ROUND (which rounds up and down) the CELING function (which you want to round up) has no length parameter (as in ROUND(salary, -3)). So divide and multiply to get the desired result:

select employee_name, ceiling(salary / 1000.0) * 1000.0 as salary
from mytable;

Try below CEILING method :

  DECLARE @fraction MONEY = 1000

   SELECT CEILING(113750/@fraction)*@fraction 
   SELECT CEILING(124300/@fraction)*@fraction
   SELECT CEILING(105100/@fraction)*@fraction 

You can use the CEILING function.



Read this for further details

Short summary from the above link:

In SQL Server (Transact-SQL), the CEILING function returns the smallest integer value that is greater than or equal to a number.

try this.

select EMPLOYEE_NAME, ceiling(salary / 1000.0) * 1000.0 as SALARY from table;

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.

Leave a Reply

Your email address will not be published. Required fields are marked *