simple division problem

[Go1369]

I am trying to divide one number by another and I can't get it to
return the right value....

in general I want to see how many fruits I have, then get the number of
apples and divide the apples by that total to get the percentage of
apples

I have

Declare @Val decimal(6,2)
Set @Val=
(
(select count(*) from Fruits where fruit='apples') /* = #APPLES
/
(select count(*) from Fruits) /* = TOTAL FRUITS
)
SELECT @Val

in my actual case the apples=7 and the fruits=8

but I can only return 87 or 875 (by multiplying by 100 or 1000) but I
can't get 87.5, which is the value I need.

[Hugo Kornelis]

On 2 Mar 2005 12:56:28 -0800, Go****@Yahoo.Com wrote:

I am trying to divide one number by another and I can't get it to
return the right value.... (snip)Declare @Val decimal(6,2)
Set @Val=
(
(select count(*) from Fruits where fruit='apples') /* = #APPLES
/
(select count(*) from Fruits) /* = TOTAL FRUITS
)
SELECT @Val

Hi Go1369,

This is the result of type handling by SQL Server. Both arguments to the
division operator are integers, therefore the result of the division is
integer as well; it only gets converted to decimal(6,2) after the
remainder is already lost.

If you cast at least one of the operators to decimal, you'll get better
results:

Declare @Val decimal(6,2)
Set @Val = (select count(*) from Fruits where fruit='apples')
/ CAST((select count(*) from Fruits) AS decimal(6,2))
SELECT @Val

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)