Division operator

I'm trying to perform a simple mathematical operation on an integer. The integer is the result of the COUNT() function, I need to divide this by a number, say 5, and then round up that number.

For example, if the result of the operation was 52.4, it would become 53, if it were 52.6 it would be 53 and If it were 52, it would remain 52.

Is this kind of functionality built in to T-SQL ?

The main problem I'm having is that the result of COUNT(blah)/5 returns an integer, and ignores the remainder.

Thanks,
PaulIf you look up "division" in BOL you will get a better understanding of what is happenig.

you might try "cast(count(blah) as float)/5"

Division by zero and computed by column

I've got two integer columns in the table, third one is computed by previous two ones division. That's fine, however sometimes can happen that divided by column is set to zero. How can i avoid division by zero exception, please? TIA

The best way to avoid the problem is to use a CASE statement like:

declare @.aTable table(value1 int, value2 int)
insert into @.aTable values (32, 4)
insert into @.aTable values (5, 0)

select value1,
value2,
case when value2 <> 0 then value1/value2 end
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0 NULL
*/

-- Or maybe:

select value1,
value2,
isnull(convert(varchar(11), case when value2 <> 0 then value1/value2 end), '')
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0
*/

|||

Thank you for your reply.

So you prefer to get the result via select command instead of fixed computed by column, right?

|||I am not sure that I understand that last question; what exactly do you mean?|||

You can use the CASE structure in a computed column definition:

Something like:

Code Snippet

ALTER TABLE MyTable

ADD COLUMN MyComputedCol AS ( CASE WHEN ( [Col1] <> 0 ) THEN ( [Col2] / [Col1] ) END )

|||Thanks for picking me up, Arnie! Again, I am asleep at the wheel! Sheesh! To answer your question, Jan, no, I have no issue against using the computed column. As Anie indicated, the computed column should be just fine. (I'm BRAINDEAD today!)|||Wow, that's amazing. Thank you Kent and Arnie!