SQL arithmetic not working as expected

I’m working on the SQL project “Analyzing Heart Disease” and some arithmetic doesn’t seem to be working as I would expect.


Step 6 of the exercise asks you to perform some math to find the percentage of active and inactive patients in a database. You have a table of active patients and a table of inactive patients. You then create “dummy” tables and records in order to perform math on these values.

For instance, you create the table and column total_active.active which contains one record which is the number of active patients. The same goes for total_inactive.inactive and total_patients.total.

My issue is that I’d like to perform the order of operations slightly differently than the lesson hint because it’s more intuitive for me.

So instead of using

total_active.active * 100 / total_patients.total

I’d like to do the very minor change of

total_active.active / total_patients.total * 100

The problem I’m having is that for some reason the second option produces a result of ‘0’, but that’s obviously not true.

Any help in understanding this would be greatly appreciated.

Original code with correct output:

Revised code with incorrect output:

That’s because SQL works weird when it comes to dividing integers.
It basically rounds then down (or floor them) or a better way to put it would be to say that SQL drops the decimal values.

You have to convert them to decimal first if you want your method to work.

Take this as an example:

50/100 * 100

This should = 50%, but in SQL 50/100 = 0 because the answer is smaller than 1 (= 0.5).
So with that said, if you first multiply it and then divide it (50 * 100 / 100), the first value is bigger than 1 (50*100 = 5000) and this gives an accurate answer, but only to the closest whole number.

If you have 50 * 100 / 150, the actual % should be 33.333333%, but because of what I explained earlier, SQL will return 33.

You can make both methods accurate by converting the values to decimal though:
50.00 * 100.00 / 150.00 = 33.333333;
50.00 / 150.00 * 100.00 = 33.333333;

You can also do it like this:
CONVERT(decimal, 50) / CONVERT(decimal, 150) * CONVERT(decimal, 100)

1 Like

Exactly the explanation I was looking for! Thank you! I had a suspicion that something like this was happening. I was hoping that SQL would store the calculation as a decimal though and then just store the result as an integer, but it’s clear that’s not the case.

Thank you for the two methods for converting integer types to decimal types. That will be very handy.