drumrboy44
asked on
Havng trouble with division - How do I avoid integer division? URGENT
Basically the title said it, i do a regular divide and keep getting integers, here's an example:
SELECT d.DonorType, d.DonorName, SUM(c.Amount) as YTD,
(SUM(c.Amount)/d.ProjContr
FROM Donors d, Contributions c
WHERE d.DonorName = c.DonorName
GROUP BY d.DonorType, d.DonorName
Please any help would be greatly appreciated.
SELECT d.DonorType, d.DonorName, SUM(c.Amount) as YTD,
(SUM(c.Amount)/d.ProjContr
FROM Donors d, Contributions c
WHERE d.DonorName = c.DonorName
GROUP BY d.DonorType, d.DonorName
Please any help would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
One more thing I should explain... I originally wrote the expression as
100.0*SUM(c.Amount)/d.Proj
ie. without parentheses, but that risks overflow. In that case, you could wrap some or all of the expression with an explicit convert(numeric([...]), [...]), but that gets messy.
Here, it was convenient that you were already multiplying by 100, and we could just rewrite 100 as 100.0 to make it clear we meant a numeric value rather than an integer.
But if you hadn't done that, you can still do this trick by prefixing with "1.0*[...]". Doesn't change the value of the expression, but it forces a numeric datatype.
100.0*SUM(c.Amount)/d.Proj
ie. without parentheses, but that risks overflow. In that case, you could wrap some or all of the expression with an explicit convert(numeric([...]), [...]), but that gets messy.
Here, it was convenient that you were already multiplying by 100, and we could just rewrite 100 as 100.0 to make it clear we meant a numeric value rather than an integer.
But if you hadn't done that, you can still do this trick by prefixing with "1.0*[...]". Doesn't change the value of the expression, but it forces a numeric datatype.
Leon