title: Calculating VAT / tax on a total in MySQL - with VAT rate depending on a location field
body: <p>I'm trying to create a report in MySQL (from tables in a Wordpress/Woocommerce installation, but the way this shop does tax is different to the way Woocommerce does things, so I can't use the Woocommerce reports.) </p>
  
  <p>The calculation needs to work out a tax rate of 20% (UK VAT) from the total - but only if the customer is in a particular location.  So, if the customer is in 'world' the tax rate is 0%. If the customer is in UK, the tax rate is 20%.  But the total charged remains the same (so more profit is made from exports than local sales!) </p>
  
  <p>I started with : </p>
  
  <pre><code>SELECT TRIM(LEADING 'Order &amp;ndash;' FROM post_title) AS Date,
  m1.meta_value AS _order_number,
  m2.meta_value AS _Location,
  m3.meta_value AS _order_total
  FROM wp_posts 
  LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
  LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
  LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
  WHERE wp_posts.post_type = 'shop_order'
  </code></pre>
  
  <p>That gave me a table like this: </p>
  
  <pre><code>Date     |  Order no  | Location    |  Order Total
  
  May 2013 |  123       |World         |1124.00
  
  Jan 2013 |  124       |UK            |163.00
  </code></pre>
  
  <p>So far so good.  So then I tried to add some SUMs to work out the amount of VAT. </p>
  
  <pre><code>SELECT TRIM(LEADING 'Order &amp;ndash;' FROM post_title) AS Date,
  m1.meta_value AS _order_number,
  m2.meta_value AS _Location,
  m3.meta_value AS _order_total,
  SUM(m3.meta_value*20/120) AS _VAT,
  SUM(m3.meta_value*100/120) AS _NET
  FROM wp_posts 
  LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
  LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
  LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
  WHERE wp_posts.post_type = 'shop_order'
  </code></pre>
  
  <p>I thought this would give me: </p>
  
  <pre><code>Date       |     Order no | Location  |    Order Total | VAT  |   NET
  
  May 2013  |             123|   World |    1124.00  |    187.33 |   1311.33
  
  Jan 2013  |         124  |    UK     |      163.00   |    27.17 |  190.17
  </code></pre>
  
  <p>And then I could go on to try to work out how to change the rate by location. 
  Unfortunately, it seems to SUM everything, rather than giving me results row by row. </p>
  
  <p>1) what have I screwed up that it sums everything together?</p>
  
  <p>2) any ideas on how best to apply the VAT calculation only when _shipping_method_title=UK? </p>
  
  <p>3) Clearly, this only works till the tax rates change.  Is my best bet for dealing with that to just limit this report by date at the point when the tax rate is next amended, and make a new report for future orders to which new tax rates apply?  Or is there a cleverer way?</p>
  
  <p>I appreciate that charging a flat rate to the customer and paying variable tax is a slightly odd approach but I don't get to change that, I am just tasked with providing the report. :-( </p>
  
  <p><strong>EDIT</strong>
  Thanks to Gordon, I have sorted out my SUM problem, and now my query looks like this and produces the table layout I expected, as shown above: </p>
  
  <pre><code>SELECT TRIM(LEADING 'Order &amp;ndash;' FROM post_title) AS Date,
  m1.meta_value AS _order_number,
  m2.meta_value AS _Location,
  m3.meta_value AS _order_total,
  SUM(m3.meta_value*20/120) AS _VAT,
  SUM(m3.meta_value*100/120) AS _NET
  FROM wp_posts 
  LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
  LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
  LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
  WHERE wp_posts.post_type = 'shop_order'
  GROUP BY wp_posts.ID
  </code></pre>
  
  <p>But I'm still not sure how to change the multipliers based on the value in the location field.  Is that possible, or am I barking up the wrong tree here?  </p>
  
  <p><strong>EDIT 2</strong></p>
  
  <p>I've worked out a partial solution - instead of the SUM lines I used a CASE  to give me a calculated value for VAT. </p>
  
  <pre><code>CASE 
          when m2.meta_value = 'World Delivery' then 0
          when m2.meta_value = 'UK Delivery' then  (m3.meta_value*20/120)
          when m2.meta_value = 'European Delivery' then  (m3.meta_value*20/120)
  END AS _VAT    
  </code></pre>
  
  <p>Unfortunately I can't just repeat the CASE statement for the _NET value (price - VAT, as it's the same CASE as VAT, so creates an error. </p>
  
  <p>I want to do something like</p>
  
  <pre><code> SUM(_order_total-_VAT) AS _NET
  </code></pre>
  
  <p>But *_order_total*  and *_VAT* are not recognised as columns that can be calculated from.</p>
  
  <p><strong>FINAL EDIT</strong>
  Thanks again to Gordon, my final query looks like this, I'm just adding it in case someone else might find it helpful.  This query works with the Wordpress Exports and Reports wordpress plugin, so can quickly be integrated into the Wordpress admin and exported to Excel, which is nifty. </p>
  
  <pre><code>SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
  m1.meta_value AS _order_number,
  m2.meta_value AS _Customer_Location,
  m3.meta_value AS _order_total,
  
  ROUND(SUM(case when m2.meta_value = 'World Delivery' then 0 else m3.meta_value*20/120 end),2) AS _VAT,
  ROUND(SUM(case when m2.meta_value = 'World Delivery' then m3.meta_value else m3.meta_value*100/120 end),2) AS _NET
  FROM wp_posts 
  LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
  LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
  LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
  WHERE wp_posts.post_type = 'shop_order' AND wp_comments.comment_post_id = wp_posts.ID  AND wp_comments.comment_content="Order status changed from processing to completed."
  GROUP BY wp_posts.ID
  </code></pre>