CODEWITHSUNDEEP
sql-serverstored-proceduresrounding
Martin
Martin

Reputation: 24318

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 its 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

Upvotes: 10

Views: 24182

Answers (8)

JosephStyons
JosephStyons

Reputation: 58745

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

Upvotes: 4

datagod
datagod

Reputation: 1051

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.

Upvotes: 0

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

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

Upvotes: 0

Guffa
Guffa

Reputation: 700562

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.)

Upvotes: 8

Mark SQLDev
Mark SQLDev

Reputation: 539

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

Upvotes: 0

Sadhir
Sadhir

Reputation: 423

Try 

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

Upvotes: 0

Donnie
Donnie

Reputation: 46933

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

Upvotes: 3

Roopesh Shenoy
Roopesh Shenoy

Reputation: 3447

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 )

Upvotes: 15

Related Questions