Rounding UP in SQL Server?

I have written a paging system for sql server. and it works great but i am trying to return how many pages there are in total

Hence if there are 5 records and 2 records per page then the total is 3 pages

this is what i have

   SELECT @TotalPages = ( (SELECT COUNT(*) FROM #TempItems) / @RecsPerPage )

and my output parameter is defined like so

   @TotalPages AS INT OUT,

Now it sort of works :-) in my test there are 5 records and 2 records per page so the above select returns 2 but 开发者_如何学Pythonits wrong it should be 3

This is because its saying 5 / 2 = whole number 2... how do i round up...?

I tired ceiling but couldn't get it to work..

Any ideas?

Thanks in advance

Did you try Casting either the numerator and the denominator as float and then using Cieling?

Integer operations always give integers. Try the following -

SELECT @TotalPages = CEILING((SELECT cast(COUNT(*) as float) FROM #TempItems) / @RecsPerPage ) 

You can use integer arithmetics for that:

SELECT @TotalPages = ((SELECT COUNT(*) FROM #TempItems) + @RecsPerPage - 1) / @RecsPerPage

(I figured out that formula many, many years ago (before there was an internet where you could ask thinks like this), and have used it many times.)

SQL Server will always give an integer result when you divide two integers.

You can add a ".0" to the end of actual hard-coded values, or multiply by "1.0" to convert a field value. If you want to round up, then the best way I know is to add 0.5, then call the normal ROUND function. That works because ROUND_DOWN(number + .5) is always the same as ROUND_UP(number).

You can also manually cast a field to a float, as others have pointed out.

But note that

round(1.0 / 2.0,0)

will return a floating point result, whereas

round(cast(1 as float) / cast(2 as float),0)

will give an integer result. So use the one that works best for you.

All of these are illustrated in the below example:

SELECT
  ROUND(5/2,0) AS INTEGER_EXAMPLE
 ,ROUND((5.0 / 2.0),0) AS FLOATING_POINT_EXAMPLE
 ,ROUND(CAST(5 AS FLOAT) / CAST(2 AS FLOAT),0) AS CASTING_EXAMPLE
 ,ROUND((5 * 1.0) / (2 * 1.0),0) AS CONVERTED_TO_FP_EXAMPLE
 ,ROUND(((1 * 1.0) / (4 * 1.0)) + (1.0 / 2.0),0) AS ROUNDED_UP_EXAMPLE

Here is a function that will do round-ups for you, along with a SELECT to show you the results it gives.

create function roundup(@n float) returns float
as
begin
  --Special case: if they give a whole number, just return it
  if @n = ROUND(@n,0)
    return @n;
    
  --otherwise, add half and then round it down
  declare @result float = @n;
  declare @half float = (1.0 / 2.0);
  set @result = round(@n + @half,0);
  
  return @result;
end
GO
select
  dbo.roundup(0.0) as example_0
 ,dbo.roundup(0.3) as example_pt_three
 ,dbo.roundup(0.5) as example_pt_five
 ,dbo.roundup(0.9) as example_pt_nine

Integer math doesn't round, it truncates. Change @RecsPerPage to be a float instead of int, and then use ceil in your sql statement.

Try

SELECT @TotalPages = CEILING( (SELECT COUNT(*) FROM #TempItems) * 1.0/ @RecsPerPage ) 

Here some similar code. The CAST functions keep SQL from automatic rounding. Adjust the 0.00 to the precision you want.

declare @C int
declare @D int

SET @C = 5
SET @D = 2

DECLARE @Answer FLOAT

SELECT @Answer = CAST(@C as FLOAT) / CAST(@D as FLOAT)

SET @Answer = CASE WHEN @Answer - ROUND(@Answer, 0) > 0.00 THEN ROUND(@Answer, 0) + 1
                   ELSE ROUND(@Answer, 0) END

PRINT @Answer

edit - correct rounding param. to 0

DECLARE @Count int
SELECT @Count = COUNT(*) FROM #TempItems
SELECT @TotalPages = @Count / @RecsPerPage 
IF @Count % @RecsPerPage > 0 SET @TotalPages = @TotalPages +1

Keep your original formula, but you need to check to see if the remainder is > 0, and if so just add 1 to your result.