Calculate percentage in MS SQL Server with int columns

Question

I am trying to calculate the total amount for charity. Each row has a bid amount and a charity percentage. The structure of the fields looks like this:

CurrentBid INT CharityPercentage INT <-- 0-100, where 100 is donating all to charity

So i tried this:

SELECT CurrentBid, CharityPercentage, CurrentBid * (CharityPercentage / 100) AS TotalCharity FROM Items

However it yields this:

50  100 50
70  100 70
25  50  0
50  25  0
30  10  0
55  15  0

It works as long as the percentage is 100. Anything below and its 0. My guess is that it has to do with the int column.

Any ideas on how to solve it? I have also tried to convert the int using a CAST in the select statement but that did not work. Its a very big procedure to change the INT column to something else.

Answer 1

Explicitly make at least one value a decimal/float/etc. In your case, you already have a literal numeric value, 100; so, simply change it to, 100.0:

CurrentBid * CharityPercentage / 100.0 AS TotalCharity

Answer 2

don't CAST -- instead, just multiply by 1.00. This does the CASTing implicitly.

SELECT CurrentBid, CharityPercentage, (1.00 * CurrentBid * CharityPercentage) / 100 AS TotalCharity FROM Items