SQL Rounding Error when calculating percentages

For cross compatability between sql and mysql servers when we do a division on a aggregated variable like count
we need to promote the INT to a DOUBLE type by multiplying it by 1.00 otherwise our results will loose precision


1/2=0 Incorrect Results
1.00/2=.5 or 1.00/2.00=.5 Good

For sake of consistency both variables have been promoted to DOUBLE

SELECT ROUND((COUNT(FieldA) * 1.00) / (COUNT(FieldB) * 1.00) * 100, 0)

Leave a Comment

Your email address will not be published. Required fields are marked *