Division question

When I do the following query:
select convert(decimal(8,2),(1/3)) -- > returns .00
When I do the following query:
select convert(decimal(8,2),convert(float,1)/convert(float,3)) -- > returns
.33
Is there a better way to do the division other than converting each number
to float before dividing them ?
Thank you.I usually do this select 1.0*1/3
http://sqlservercode.blogspot.com/|||Hi Paul,
The reason that your first SQL statement returns .00 because it treated the
number 1 and 3 as integers and "if an integer is divided by an integer, the
result is an integer that has any fractional part of the result truncated.".
Therefore, only a integer is returned for the convert function. i.e. 0 -> .0
0.
So you do need to convert the numbers first unless you use this:
Select (1.0/3.0)
Hope this help.
Dennis Lam
"Paul fpvt2" wrote:

> When I do the following query:
> select convert(decimal(8,2),(1/3)) -- > returns .00
> When I do the following query:
> select convert(decimal(8,2),convert(float,1)/convert(float,3)) -- > return
s
> .33
> Is there a better way to do the division other than converting each number
> to float before dividing them ?
> Thank you.

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

divide INT get decimal output

I have written the following query and even though I used CAST if the
number is less then 1 it show up as 0. Here is the query:

select cast(count(SafetyTrainingYN) AS FLOAT)
from ICPCDATA
where SafetyTrainingYN = 1 /
(select count(SafetyTrainingYN)
from ICPCDATA)

Any help would be much appreciated.

Thanks,

jp

remove _nospam_ for my email

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Josh, is this what you're after?

SELECT
(SELECT CAST(COUNT(*) AS FLOAT)
FROM ICPCDATA
WHERE SafetyTrainingYN=1) /
(SELECT COUNT(SafetyTrainingYN)
FROM ICPCDATA)
AS PercentYes

In your query, you're looking for the count of rows where SafetyTrainingYN
is equal to the fraction 1/count(...) (zero when the table has more than 1
row due to integer division).

Hope that helps,
Rich

"Josh Phillips" <phillips3_nospam_@.hotmail.com> wrote in message
news:40914d9e$0$203$75868355@.news.frii.net...
> I have written the following query and even though I used CAST if the
> number is less then 1 it show up as 0. Here is the query:
> select cast(count(SafetyTrainingYN) AS FLOAT)
> from ICPCDATA
> where SafetyTrainingYN = 1 /
> (select count(SafetyTrainingYN)
> from ICPCDATA)
> Any help would be much appreciated.
> Thanks,
> jp
>
> remove _nospam_ for my email
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!