SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Synopsis: Rounding Errors

Synopsis: Rounding Errors

Let's see how using integers for fractions changes the result of sensitive calculations.

We'll cover the following...

Let’s imagine that your boss asks you to produce a report of the cost of programmer time for the project, based on the total work needed to fix each bug. Each programmer in the Accounts table has a different hourly rate, so you record the number of hours required to fix each bug in the Bugs table, and you multiply it by the hourly_rate of the programmer assigned to do the work.

MySQL
SELECT b.bug_id, b.hours * a.hourly_rate AS cost_per_bug
  FROM Bugs AS b 
  JOIN Accounts AS a ON (b.assigned_to = a.account_id);

It would be best to create new columns in the Bugs and Accounts ...