title: Calculating tax correctly using Mysql and php
body: <p>I am trying to re-calculate tax on a sale after it is entered into a database. I can calculate the information correctly before storing in the database, but calculating it from the database for reporting purposes has proved troublesome due to rounding errors.</p>
  
  <p>The application displays an interface that allows one to add items and view the subtotal, taxes and totals.</p>
  
  <p>Example input:</p>
  
  <p>Item 1: 9.95</p>
  
  <p>Item 2: 2.95</p>
  
  <p><strong>Subtotal: 12.90</strong></p>
  
  <p>Taxes (could be different for each item, but now they are all the same for each item)</p>
  
  <p>2.900%: 0.37</p>
  
  <p>1.000%: 0.13</p>
  
  <p>1.100%: 0.14</p>
  
  <p>3.000%: 0.39</p>
  
  <p><em>Tax Total</em>: 1.03</p>
  
  <p><strong>Total: 13.93</strong></p>
  
  <p>The way I calculate taxes before storing into database:</p>
  
  <pre><code>foreach Item as $price
  {
    foreach Tax as $percent
    {
      $taxes[$percent]+=$price*($percent/100);
    }
  }
  
  foreach($taxes as $percent =&gt; $tax_amount)
  {
    $taxes[$percent] = ROUND_TO_2_DECIMALS($tax_amount);
  }
  </code></pre>
  
  <p>After calculating all this information I store the information in a database that can be retrieved later for reporting purposes.</p>
  
  <p>Example Schema:</p>
  
  <pre><code>phppos_sales_items
  - sale_id
  - item_id
  - price
  
  phppos_sales_items_taxes
  - sale_id
  - item_id
  - tax_percent
  </code></pre>
  
  <p><strong>Sample Data for phppos_sales_items table</strong></p>
  
  <p>sale_id: 1, item_id: 1, item_unit_price: 9.95</p>
  
  <p>sale_id: 1, item_id: 2, item_unit_price: 2.95</p>
  
  <p><strong>Sample Data for phppos_sales_items_taxes table</strong></p>
  
  <p>sale_id: 1, item_id: 1, percent: 2.90</p>
  
  <p>sale_id: 1, item_id: 1, percent: 1.00</p>
  
  <p>sale_id: 1, item_id: 1, percent: 1.10</p>
  
  <p>sale_id: 1, item_id: 1, percent: 3.00</p>
  
  <p>sale_id: 1, item_id: 2, percent: 2.90</p>
  
  <p>sale_id: 1, item_id: 2, percent: 1.00</p>
  
  <p>sale_id: 1, item_id: 2, percent: 1.10</p>
  
  <p>sale_id: 1, item_id: 2, percent: 3.00</p>
  
  <p>Sample Query to calculate tax for a sale</p>
  
  <pre><code>(SELECT SUM(tax) FROM (SELECT ROUND((item_unit_price)*(SUM(percent)/100),2) as tax
  FROM phppos_sales_items
  INNER JOIN phppos_sales_items_taxes USING (sale_id, item_id)
  WHERE sale_id = 1
  GROUP BY sale_id, item_id) as tax)
  </code></pre>
  
  <p>Output is <code>1.04</code> when I need to get <code>1.03</code>. This query works in most cases, but it fails a lot to when there are rounding problems.</p>
  
  <p>I need to be able to generate summary informatation accuratly using sql. I am open to any suggestions on how to calculate information before the sale and in the database.</p>