Division not returning whole numbers and not decimal values

I'm trying to divide 2 int columns and am expecting a result in a decimal
value, however, only whole values are being returned. First I tried
returning a calculated value from a select statement like this:
CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
INSERT INTO #tmp (col1, col2)
VALUES (7,5)
SELECT col2 / col1
FROM #tmp
But it returned zero. So then I tried to update the calculated value into a
decimal column but I got the same result.
UPDATE #tmp
SET TEST = col2 / col1
SELECT col1, col2, TEST FROM #tmp
Any help on this? THANKS!
moondaddy@.nospam.nospamYou've got to convert at least 1 of the values to a floating-point or decima
l
type first. e.g:
select 4 / 3 -- returns int
select convert(float, 4) / 3 -- returns float
"moondaddy" wrote:

> I'm trying to divide 2 int columns and am expecting a result in a decimal
> value, however, only whole values are being returned. First I tried
> returning a calculated value from a select statement like this:
>
> CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
> INSERT INTO #tmp (col1, col2)
> VALUES (7,5)
> SELECT col2 / col1
> FROM #tmp
> But it returned zero. So then I tried to update the calculated value into
a
> decimal column but I got the same result.
> UPDATE #tmp
> SET TEST = col2 / col1
> SELECT col1, col2, TEST FROM #tmp
> Any help on this? THANKS!
> --
> moondaddy@.nospam.nospam
>
>

Division

Newbie Question.
While using a query/subquery to get a percentage I keep getting zero. I
assume this is becuase the number returned is less than 1. How can I make
this work right?
Example
Select 123/12345
returns 0 when it should equal 0.00996
I have tried converting using decimal(5,5) but either this is wrong, or I am
doing it wrong.
Thanks
DaveThe result of an integer division will be integer.
Example:
select 4 / 2, 4.00 / 2, 4 / 2.00, cast(2 as numeric(5, 2)) / 4
AMB
"Dave S." wrote:

> Newbie Question.
> While using a query/subquery to get a percentage I keep getting zero. I
> assume this is becuase the number returned is less than 1. How can I make
> this work right?
> Example
> Select 123/12345
> returns 0 when it should equal 0.00996
> I have tried converting using decimal(5,5) but either this is wrong, or I
am
> doing it wrong.
> Thanks
> Dave
>
>|||Select 123/(12345 * 1.0)
Keith
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:uUS4OO5EFHA.1932@.TK2MSFTNGP14.phx.gbl...
> Newbie Question.
> While using a query/subquery to get a percentage I keep getting zero. I
> assume this is becuase the number returned is less than 1. How can I make
> this work right?
> Example
> Select 123/12345
> returns 0 when it should equal 0.00996
> I have tried converting using decimal(5,5) but either this is wrong, or I
am
> doing it wrong.
> Thanks
> Dave
>