select mynumber , mynumber/1000.0 as dividedby1000 , ceiling(mynumber/1000.0) as ceilingresult , ceiling(mynumber/1000.0)*1000 as myfinalnumber from mynumbers
CEILING returns the smallest integer which is equal to or greater than the number it is given. Notice that we need a complex expression involving CEILING, in order to get the "rounded up to next 1000" number that you wanted. The "trick" (if you want to call it that) is to divide by 1000.0, which forces a decimal result, before applying the CEILING.
The query above also illustrates how to go about testing a complex expression like this. Build it up, one step at a time, in separate columns.
The results of the query on the data you provided are:
mynumber dividedby1000 ceilingresult myfinalnumber 460 0.460000 1 1000 1000 1.000000 1 1000 1001 1.001000 2 2000 1550 1.550000 2 2000 2001 2.001000 3 3000
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.