How come my division yields wrong numbers?

If your division is either 0 or 100 and can’t see the real percentage, try these 2 solutions:

Solution 1: Add a float to the equation

SELECT
((100.0 * (no_positive_on - no_negative_on))/no_positive_on) as "% Positive"
FROM bio_data

In SQL 1 / 2 is 0 but 1.0 / 2 is 0.5. In the query above, “no_positive_on” and “no_negative_on” are integers, so all operations between them will yield integers including division. If instead one number in the equation was a float, then the result would be a float.

Solution 2: Cast existing numbers to a float

SELECT
(((no_positive_on - no_negative_on)::float/no_positive_on::float) * 100) as "% Positive"
FROM bio_data

In SQL and other programming languages, “casting” means “converting a data type”. Similar to the query above, you can convert integers into floats.

Additional resource

https://stackoverflow.com/questions/3443672/integer-division-in-sql-server/3443707#3443707